Me, Oracle or the schema?

From: Charlie Mengler <charliem_at_mwh.com>
Date: Thu, 11 May 2000 06:29:22 -0700
Message-Id: <10494.105375@fatcity.com>

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 -------- Subject: Me, Oracle or the schema? Date: Wed, 10 May 2000 08:38:24 -0700 From: Charlie Mengler <charliem_at_mwh.com> 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));

Received on Thu May 11 2000 - 08:29:22 CDT