Hi All,
I have requirement to find all parent child tables ,I have written below query for doing this.
Please validate and let me know if there are any other better ways of doing this..
[code]
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
[/code]
[code]
DECLARE
CURSOR cur_main
IS
SELECT puc.table_name AS parent_table,
pucc.column_name parent_pk_column ,
puc.constraint_name parent_pk_constraint_name,
cuc.table_name AS child_table_name ,
cuc1.constraint_name AS child_pk_constraint_name,
cucc.column_name AS child_pk_column
FROM user_constraints puc ,
user_cons_columns pucc,
user_constraints cuc ,
user_constraints cuc1,
user_cons_columns cucc
WHERE puc.table_name ='P'
AND puc.constraint_type ='P'
AND puc.constraint_name =pucc.constraint_name
AND puc.constraint_name =cuc.r_constraint_name
AND cuc.constraint_type ='R'
AND cuc.table_name =cuc1.table_name
AND cuc1.constraint_type ='P'
AND cuc1.constraint_name =cucc.constraint_name ;
g_level number(10) :=0;
v_parent_table varchar2(30);
v_child_table varchar2(30);
PROCEDURE pr_print_parent_child(
p_child_tble_name IN VARCHAR2,
p_pk_constraint_name IN VARCHAR2)
IS
v_child_table_cnt NUMBER(20):=0;
CURSOR cur_parent_child (cp_table_name IN VARCHAR2)
IS
SELECT puc.table_name AS parent_table,
pucc.column_name parent_pk_column ,
puc.constraint_name parent_pk_constraint_name,
cuc.table_name AS child_table_name ,
cuc1.constraint_name AS child_pk_constraint_name,
cucc.column_name AS child_pk_column
FROM user_constraints puc ,
user_cons_columns pucc,
user_constraints cuc ,
user_constraints cuc1,
user_cons_columns cucc
WHERE puc.table_name =cp_table_name
AND puc.constraint_name =p_pk_constraint_name
AND puc.constraint_type ='P'
AND puc.constraint_name =pucc.constraint_name
AND puc.constraint_name =cuc.r_constraint_name
AND cuc.constraint_type ='R'
AND cuc.table_name =cuc1.table_name
AND cuc.table_name <>cp_table_name--Parent table should not be same as child table
AND cuc1.constraint_type ='P'
AND cuc1.constraint_name =cucc.constraint_name ;
type rec_child_tbl
IS
record
(
child_table VARCHAR2(3000),
pk_constraint_name VARCHAR2(3000) );
type ty_child_tbl
IS
TABLE OF rec_child_tbl;
t_child_tbl ty_child_tbl:=ty_child_tbl();
BEGIN
SELECT COUNT(cuc.r_constraint_name)
INTO v_child_table_cnt
FROM user_constraints puc ,
user_constraints cuc
WHERE puc.constraint_name=p_pk_constraint_name
AND puc.constraint_name =cuc.r_constraint_name;
IF v_child_table_cnt >0 THEN
FOR j IN cur_parent_child( p_child_tble_name)
LOOP
dbms_output.put_line('parent_table_name-->'||j.parent_table||','||'Child_table_name-->'||j.child_table_name);
v_child_table :=j.parent_table;
if v_parent_table<>v_child_table
then
g_level:=g_level+1;
v_parent_table:=j.parent_table;
V_CHILD_table:=j.child_table_name;
end if ;
INSERT
INTO parent_child_rel VALUES
(
parent_child_rel_seq.nextval,
j.parent_table,
j.child_table_name,
g_level
);
t_child_tbl.extend();
t_child_tbl(t_child_tbl.last).child_table :=j.child_table_name;
t_child_tbl(t_child_tbl.last).pk_constraint_name:=j.child_pk_constraint_name;
-- pr_print_parent_child(j.child_table_name,j.child_pk_constraint_name);
END LOOP;
ELSE
RETURN;
END IF ;
IF t_child_tbl.count>0 THEN
FOR k IN t_child_tbl.first..t_child_tbl.last
LOOP
NULL;
-- dbms_output.put_line('CHILD_TABLE iN LOOP'||t_child_tbl(k).child_table);
-- DBMS_OUTPUT.PUT_LINE('pk_constraint_name iN LOOP'||t_child_tbl(K).pk_constraint_name);
-- g_level:=g_level+1;
pr_print_parent_child(t_child_tbl(k).child_table,t_child_tbl(k).pk_constraint_name);
END LOOP;
end if ;
t_child_tbl.delete(t_child_tbl.first,t_child_tbl.last);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line (SQLCODE);
dbms_output.put_line (sqlerrm||dbms_utility.format_error_backtrace());
end pr_print_parent_child;
BEGIN
FOR i IN cur_main
LOOP
dbms_output.put_line
(
'parent_table_name-->'||i.parent_table||','||'Child_table_name-->'||i.child_table_name
)
;
g_level :=1;
v_parent_table:=i.parent_table;
INSERT
INTO parent_child_rel VALUES
(
parent_child_rel_seq.nextval,
i.parent_table,
i.child_table_name,
g_level
);
pr_print_parent_child(i.child_table_name,i.child_pk_constraint_name);
-- g_level:=1;
END LOOP ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error Code --->'||SQLCODE);
dbms_output.put_line('Error Message--->'||sqlerrm||dbms_utility.format_error_backtrace());
END ;
[/code]
Thanks,
P Prakash