Hi folks,
I need to treat a failure in query which contain "CONNECT BY" clause:
Code:
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