| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Me, Oracle or the schema?
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();
> -----Original Message-----
> From: "Charlie Mengler" <charliem_at_mwh.com> [mailto:charliem_at_mwh.com]
> Sent: Thursday, May 11, 2000 3:37 PM
> To: smtp_at_inl001@servers["Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>]
> Subject: Me, Oracle or the schema?
>
>
> Please forgive me if this appears as a duplicate post.
> I posted this yesterday morning, but never saw it or any
> other posts all day.
> Also I never got any feedback & I'm still trying to figure
> why it does not
> successfully analyze one specific schema I support. This is a
> 3rd party
> package that I've been asked to bypass their pretty Windoze
> GUI & do some
> loading of bulk data. Therefore it would help me immensely if
> I knew all
> the PK/FK constraints so I can load the tables in the needed order.
>
> -------- Original Message --------
> Date: Wed, 10 May 2000 08:38:24 -0700
> Organization: Maintenance Warehouse
> To: Oracle <ORACLE-L_at_fatcity.com>
>
> Below is a driver program and an function that I've trying to debug.
> The goal is to generate a report showing three values -
>
> Level Parent_Table Child_Table
>
> Where the level is the depth of the tree beneath that
> specific parent_table.
> When the level is greater than 1 then you should be able to
> find an entry
> for the child_table in the report too, etc.
>
> I'm running on Solaris V2.6 & Oracle V7.3.4.3, V8.1.5 & V8.1.6
> All the Oracle instances are essentially single schema databases.
> 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;
> regardless on the number of open_curors provided. The number of
> open cursors should equal the maximum depth of the tree; which
> should be finite & a reasonably small value (say less than 10).
>
> At this point in time, I'm not sure if I have a bug in my code,
> if there is a bug in Oracle (the problem DB is the only 1 I have
> running on V8.1.5), or if the schema has a loop in the PK/FK
> relationships; or something else is FUBAR.
>
> So I'm asking folks to try this on their databases/schemas & let
> me know the results. Just be warned that it can take many, many
> minutes to complete a run. Please note that this really should
> NOT be run as user SYS or SYSTEM. It should be run as the schema
> owner of data where you wish to get a report of the PK/FK
> relationships.
>
> TIA & HAND!
> ===================================================================
>
> set serveroutput on lines 131 pause off
> create or replace FUNCTION recurse_pk(v_constraint_name_in IN
> VARCHAR2, v_curlevel IN INTEGER, v_TableName_out OUT VARCHAR2)
> RETURN INTEGER IS
> v_ConstraintName
> user_constraints.constraint_name%type;
> v_RConstraintName
> user_constraints.r_constraint_name%type;
> v_TableName user_constraints.table_name%type;
> v_retval INTEGER;
> CURSOR constraint_cur IS
> SELECT P.TABLE_NAME, P.CONSTRAINT_NAME
> FROM USER_CONSTRAINTS C, USER_CONSTRAINTS p
> WHERE C.CONSTRAINT_TYPE = 'R'
> AND C.R_CONSTRAINT_NAME = v_constraint_name_in
> AND C.TABLE_NAME = P.TABLE_NAME
> AND P.CONSTRAINT_TYPE IN ('P','U');
> BEGIN
> DBMS_OUTPUT.ENABLE(500000);
> v_retval := v_curlevel;
> --DBMS_OUTPUT.PUT_LINE(v_curlevel || ' = ' ||
> v_constraint_name_in );
>
> OPEN constraint_cur;
> LOOP
> FETCH constraint_cur INTO v_TableName, v_ConstraintName;
> exit when constraint_cur%NOTFOUND;
> IF v_curlevel < 15
> THEN
> v_retval := v_curlevel + 1;
> v_retval :=
> recurse_pk(v_ConstraintName,v_retval,v_TableName_out);
> v_TableName_out := v_TableName;
> ELSE
> v_retval := v_curlevel;
> END IF;
> -- IF v_retval > 0
> -- THEN
> -- DBMS_OUTPUT.PUT_LINE(v_retval || ' = ' ||
> RPAD(v_TableName,32) || ' ' || RPAD(v_TableName_out,32));
> -- END IF;
> END LOOP;
> CLOSE constraint_cur;
> RETURN v_retval;
> EXCEPTION
> WHEN others then
> RAISE;
> END;
> /
>
>
> DECLARE
> v_ConstraintName
> user_constraints.constraint_name%type;
> v_TableName user_constraints.table_name%type;
> v_CTableName user_constraints.table_name%type;
> v_startlevel INTEGER :=0 ;
> v_retval INTEGER;
> CURSOR constraint_cur IS
> SELECT TABLE_NAME, CONSTRAINT_NAME
> FROM USER_CONSTRAINTS
> WHERE CONSTRAINT_TYPE = 'P'
> AND TABLE_NAME NOT IN ('LMUNITS', 'MHCLASS',
> 'MHCLAUSE', 'MHCLSTYP')
> ORDER by TABLE_NAME;
> BEGIN
> DBMS_OUTPUT.ENABLE(100000);
>
> OPEN constraint_cur;
> LOOP
> FETCH constraint_cur INTO v_TableName, v_ConstraintName;
> exit when constraint_cur%NOTFOUND;
> v_retval := recurse_pk(v_ConstraintName,0,v_CTableName);
> IF v_retval > 0
> THEN
> DBMS_OUTPUT.PUT_LINE(v_retval || ' ' ||
> RPAD(v_TableName,32) || ' ' || RPAD(v_CTableName,32));
> END IF;
> END LOOP;
> CLOSE constraint_cur;
> EXCEPTION
> WHEN others then
> RAISE;
> END;
> /
> --
> 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
Received on Thu May 11 2000 - 10:50:30 CDT
 |
 |