Link to home
Start Free TrialLog in
Avatar of arperumal
arperumal

asked on

Hierachical Query - ORA-01436 Error

Hi All,
           I am getting error while using Hierachical query.
           I want to avoid this error. But when the records having self-referece, we get this error.
           I need a SP to skip the self referece records. It must show what are all the records self-refereced.
           my out put must without error. Never recommend 10g or 9i option. I am using 8.1.7
Thanks
Avatar of alexfrl
alexfrl

If you use 'start with and connect by {prior}' you can avoid SELF-REFERENCING by simple WHERE CLAUSE like in the below example.

-- Show "where used" for a given component

select parent, level
  from my_bill_of_materials
where parent<>component   -- this condition avoids self-referencing as you request
start with component = '<your example>'
connect by prior component = parent
Avatar of arperumal

ASKER

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PARENT                                             VARCHAR2(10)
 CHILD                                              VARCHAR2(10)

SQL> select * from t;

PARENT     CHILD                                                                
---------- ----------                                                          
100        200                                                                  
200        300                                                                  
300        100                                                                  

SQL>  select *  from t
  2   where parent <> child
  3   connect by prior child = parent
  4   start with parent=100
  5  /
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected

SQL> spool off
Avatar of Helena Marková
Do you want to remove self-referencing from your table ?
I understand. You really have LOOPS in your table.

child->parent1->parent2>parent3->parent1

you can try to see your looping parent by :

create or repace procedure check_loop(p_start_parent varchar2) is
cursor iii is
select parent p,level l  from t
  where parent <> child
  connect by prior child = parent
   start with parent=p_start_parent

begin
      for i in iii loop
         dbms_output.put_line(i.l||': '||i.s);
      end loop;
  exception
    when others then
       dbms_output.put_line('    DEAD LOOP');
       return;
end;            
         
 
ASKER CERTIFIED SOLUTION
Avatar of alexfrl
alexfrl

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
To sign all problematic pairs do the following

1) alter table <your table> add loop_flag varchar2(1) default 'N';

2)
create or repace function is_loop(p_start_parent varchar2) return varchar2 is
cursor iii is
select parent p,level l  from t
  where parent <> child
  connect by prior child = parent
   start with parent=p_start_parent;

begin
      for i in iii loop
            null; --dbms_output.put_line(i.l||': '||i.P);
      end loop;
      RETURN 'N';
  exception
    when others then
       return 'Y';
end;      

3)
UPDATE T SET LOOP_FLAG='Y'
 WHERE IS_LOOP(PARENT)='Y';

4) select parent p,CHILD C, level l  from t
  where LOOP_FLAG='N'
  connect by prior child = parent;