ORA-01436: CONNECT BY loop in user data

[nir_s]

Hi folks,

I need to treat a failure in query which contain "CONNECT BY" clause:

SQL> Select 'DWPROD', owner, object_type, object_name, object_id, status
  2  from sys.ALL_OBJECTS
  3  where object_name like upper('%ORDER') and object_type != 'SYNONYM'
  4  union
  5  Select 'DWPROD', owner, object_type, object_name, object_id, status
  6  from sys.ALL_OBJECTS
  7  where object_id in (Select object_id
  8                      from public_dependency
  9                      connect by prior object_id = referenced_object_id
 10                      start with referenced_object_id in
 11                                                       (Select object_id
 12                                                        from sys.ALL_OBJECTS
 13                                                        where upper(object_name) like upper('%ORDER')));
Select 'DWPROD', owner, object_type, object_name, object_id, status
*
ERROR at line 1:
ORA-01436: CONNECT BY loop in user data

Please advise how to change the query to overcome this failure.

Thanks in advance,
Nir

[simply_dba]

if you are in 10g try this:

Select 'DWPROD', owner, object_type, object_name, object_id, status
    from sys.ALL_OBJECTS
    where object_name like upper('%ORDER') and object_type != 'SYNONYM'
    union
    Select 'DWPROD', owner, object_type, object_name, object_id, status
    from sys.ALL_OBJECTS
    where object_id in (Select object_id
                        from public_dependency
                        connect by nocycle prior object_id = referenced_object_id
                       start with referenced_object_id in
                                                        (Select object_id
                                                         from sys.ALL_OBJECTS
                                                         where upper(object_name) like upper('%ORDER')));

[nir_s]

Hi simply_dba,

Thanks!!
fortunately,I use 10g db and it works fantastic.

Best regards,
Nir