Re: Me, Oracle or the schema?

You can have the user run this:
select a.constraint_name, a.r_constraint_name, a.table_name, b.table_name  from dba_constraints a,

        dba_constraints b
where a.owner='CACSG'
and a.R_CONSTRAINT_NAME is not null
and a.R_constraint_name=b.constraint_name order by a.table_name;

You can have let table_name by in (select table_name from user_tables) which will give all the tables in the users schema.

HTH,
Ruth
----- Original Message -----
From: Charlie Mengler <charliem_at_mwh.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Thursday, May 11, 2000 10:37 AM
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:14:26 CDT