Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: CONNECT BY loop error & useful script

Re: CONNECT BY loop error & useful script

From: <michael_bialik_at_my-deja.com>
Date: Fri, 21 Jan 2000 12:49:44 GMT
Message-ID: <869kl4$t5s$1@nnrp1.deja.com>


Hi.

 In my case it was something like PackageA.ProcA calling PackageB.ProcB  calling PackageC.ProcC calling PackageA.ProcD.

 HTH. Michael.

In article <38877746.2FFC3DB1_at_us.ibm.com>,   Doug Cowles <dcowles_at_us.ibm.com> wrote:
> I am getting a
> ERROR at line 1:
> ORA-01436: CONNECT BY loop in user data
>
> It only happens in one of the five instances I am trying this sript
on.
> This script actually came from Oracle from their ftp site or
something,
> and is supposed to compile all invalid objects in order of their
> dependency. It's a nice little script. I'm just curious if anyone
can
> shed any light on why it bails on me in one particular instance,
> specifically, because there is no CONNECT BY reference in the script.
> I imagine it's buried somewhere in the order_object_by_dependency or
> something.
> Running oracle 8.1.5, fully loaded, on AIX 4.3.2
>
> Script is -
> set heading off
> set pagesize 0
> set linesize 79
> set verify off
> set echo off
>
> spool compile_all.tmp
> SELECT
> decode(OBJECT_TYPE, 'PACKAGE BODY',
> 'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
> 'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || '
> compile;' )
> FROM
> dba_objects A,
> sys.order_object_by_dependency B
> WHERE
> A.OBJECT_ID = B.OBJECT_ID(+) and
> STATUS = 'INVALID' and
> OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
> 'TRIGGER', 'VIEW' )
> ORDER BY
> DLEVEL DESC,
> OBJECT_TYPE,
> OBJECT_NAME;
> spool off
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jan 21 2000 - 06:49:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US