ORA-01436 when trying to export CONNECT BY PRIOR result

Question

I have a table in Oracle that has two columns: CODIGO_DA_CONTA_PAI and CODIGO_DA_CONTA_FILHO (basically means Parent and Child).

For the Parent CT-0000000000, the Children and the Children of Children, another column needs to be updated (but for now I am only trying to select the hierarchy).

When a Child has one or more Child, it is also present in the Parent column.

When I run this query shown below it shows me the results, but when I try to export, enclose by a SELECT clause, or scroll down the result set, the error message number 01436 appears to me.

From what I read, it seems to be generating a result set that will never end.

How can I make it work properly? Is my code right or am I missing something?

 SELECT
     level AS nivel,
     codigo_da_conta_pai,
     codigo_da_conta_filho,
     estrutura_ativa_filho
   FROM conta c
  START WITH c.codigo_da_conta_pai = 'CT-0000000000'
CONNECT BY PRIOR c.codigo_da_conta_filho = c.codigo_da_conta_pai

Answer 1

Without seeing the real data, it's difficult to tell what's causing the loop. You seem to start from a root (a children directly linked to a root row) and the work towards the children. In that case, the CONNECT BY condition should rather be:

CONNECT BY PRIOR C.CODIGO_DA_CONTA_PAI = C.CODIGO_DA_CONTA_FILHO

Or could it be that you have a row with both CODIGO_DA_CONTA_PAI and CODIGO_DA_CONTA_FILHO equal to 'CT-0000000000'?