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 -

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();
Received on Thu May 11 2000 - 13:12:21 CDT