RE: Me, Oracle or the schema?

> I respectfully disagree with the conclusion stated below.
> The "IF v_curlevel < 15" was added in an attempt to allow the
> job to complete
> WITHOUT going into an infinite loop. The infinite loop is/was
> the result of
> the following data -
>

Sorry but IMHO, if the "IF v_curlevel < 15" was preventing an infinite loop then that is the exit condition for a recursive function. You removed the exit condition, hence Huge bug.

Now if you think the data doesn't fit then I'd suggest that the function is probably not quite correct and you haven't yet found the currect case for the recursion in the funtion.

regards
George

> Needless to say my code was not handling the case below "properly";
> and resulted in an infinite loop. For any schema where the same index
> does NOT appear in both the CONSTRAINT_NAME field & the
> R_CONSTRAINT_NAME
> field for the SAME table, the code works WITHOUT the "IF
> v_curlevel < 15"
> test at all! Try it & see for yourself.
>
> Is it reasonable & acceptable to have the same object
> (XPKPMACTVQ) both as
> a CONSTRAINT_NAME & R_CONSTRAINT_NAME on the SAME table?
>
> 1 select table_name, constraint_name, r_constraint_name,
> constraint_type
> 2 from user_constraints
> 3* where table_name = 'PMACTVQ'
> SQL> /
>
> TABLE_NAME CONSTRAINT_NAME
> R_CONSTRAINT_NAME C
> -------------- ------------------------------
> ------------------------------ -
> PMACTVQ SYS_C002108 XPKPMACTVQ
> R
> PMACTVQ SYS_C002109 XPKPMACTV
> R
> PMACTVQ SYS_C001511
> C
> PMACTVQ SYS_C001512
> C
> PMACTVQ XPKPMACTVQ
> P
>
> P.S.
> The above is from a commercial 3rd party application that was
> purchased &
> which I must support. :-(
>
>
> George.Brennan_at_warnermusic.com wrote:
> >
> > Charlie,
> >
> > You are introducing a HUGE bug.
> >
> > > This code works as expected in all but one case. In that one
> > > case if I remove the test of "IF v_curlevel < 15" the code goes
> > > into a death spiral & eventually errors out with an ORA-1000;
> >
> > It's a recursive function and you are removing the exit
> condition so it will recurse forever or until it runs out of
> something, as you say open cursors.
> > The highlites of what is happening are
> >
> > recurse_pk()
> > OPEN constraint_cur;
> > LOOP
> > FETCH constraint_cur INTO v_TableName, v_ConstraintName;
> > recurse_pk();
> > BOOM!
> >
> > regards
> > George
> --
> Author: Charlie Mengler
> INET: charliem_at_mwh.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Fri May 12 2000 - 06:03:17 CDT