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

RESULT SET

you can try using CONNECT BY NOCYCLE PRIOR – tbone Jan 08 '14 at 20:20 — tbone, Jan 08 '14 at 20:20
Seems to have worked perfectly. Thanks @tbone! – Lucas Rezende Jan 10 '14 at 17:32 — Lucas Rezende, Jan 10 '14 at 17:32

score 0 · Answer 1 · answered Jan 08 '14 at 19:10

0

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'?

answered Jan 08 '14 at 19:10

Codo

75,595
17
168
206

No. This 'CT-0000000000' is only CODIGO_DA_CONTA_PAI. =/ – Lucas Rezende Jan 08 '14 at 19:13
There is no parent without at least one child, and the hierarchy begins with parent equals to CT-0000000000. But at the same level there will be other CTs... – Lucas Rezende Jan 08 '14 at 19:30

ORA-01436 when trying to export CONNECT BY PRIOR result

1 Answers1