| 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?
Hi Charlie,
I think your problem can be resolved by using the connect by clause of select statement. Because user_constraints is a view, we cannot use the connect by on that. Hence I created a temporary table and used the connect by clause.
Following is the code and sample output.
HTH.
Thanks
Baiju Anthony
DBA, MBT, Pune India.
select lpad(' ', 2*(level-1))||to_char(level) lvl, constraint_name,
decode(constraint_type,'P',table_name,' ') table_name,
decode(constraint_type,'R',table_name,' ') r_table_name
from temp$3956
EOS Sample Output
Lvl Constraint Name Table Name Reffering Table Name
----- -------------------- -------------------- -------------------- 1 PK_APP_NAME APP_HELP 2 FK_FORMS_HELP_APP FORMS_HELP 1 BLOB_PRIMARY_KEY BLOB 1 CUSTOMER_PRIMARY_KEY CUSTOMER 2 ORD_FOREIGN_KEY ORD 1 DEPT_PRIMARY_KEY DEPT 2 EMP_FOREIGN_KEY EMP 1 EMP_PRIMARY_KEY EMP 2 EMP_SELF_KEY EMP 1 EMP_SELF_KEY EMP 1 EMP_FOREIGN_KEY EMP 1 PK_FORMS_HELP FORMS_HELP 2 FK_FORMS_HELP FORMS_HELP 2 FK_HELP_KEYWORDS HELP_KEYWORDS 2 FK_HELP_RELATED HELP_RELATED 2 FK_HELP HELP_RELATED 1 FK_FORMS_HELP FORMS_HELP 1 FK_FORMS_HELP_APP FORMS_HELP 1 PK_HELP_KEYWORDS HELP_KEYWORDS 1 FK_HELP_KEYWORDS HELP_KEYWORDS Lvl Constraint Name Table Name Reffering Table Name ----- -------------------- -------------------- -------------------- 1 PK_HELP_RELATED HELP_RELATED 1 FK_HELP_RELATED HELP_RELATED 1 FK_HELP HELP_RELATED 1 ITEM_PRIMARY_KEY ITEM 1 ITEM_FOREIGN_KEY ITEM 1 ORD_PRIMARY_KEY ORD 2 ITEM_FOREIGN_KEY ITEM 1 ORD_FOREIGN_KEY ORD 1 PRODUCT_PRIMARY_KEY PRODUCT 1 SYS_C005045 TVTABLE 1 SYS_C005044 WORLD_CITIES
31 rows selected.
> 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
Received on Fri May 12 2000 - 03:41:53 CDT
 |
 |