DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ORA-01436: CONNECT BY loop in user data

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    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

  2. #2
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    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
  •  


Click Here to Expand Forum to Full Width