-
ORA-01436: CONNECT BY loop in user data
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
-
if you are in 10g try this:
Code:
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')));
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Hi simply_dba,
Thanks!!
fortunately,I use 10g db and it works fantastic.
Best regards,
Nir
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|