I have an Idea in my mind but I stucked in the code, I need to write a programm to get tables as tree based on the foreign, I will clearafy my case with script:
create table test_item_group
(
group_code varchar2(10),
group_desc varchar2(60),
constraint test_item_group_pk primary key (group_code)
);
create table test_items
(
item_code varchar2(10),
group_code varchar2(10),
item_desc varchar2(60),
constraint test_items_pk primary key (item_code),
constraint test_items_fk1 foreign key (group_code) references test_item_group (group_code)
);
create table test_invoices_master
(
id number,
invoice_no varchar2(10),
constraint test_invoices_pk primary key (id)
);
create table test_invoices_details
(
id number,
master_id number,
item_code varchar2(10),
qty number,
price number,
constraint test_invoices_details_pk primary key (id),
constraint test_invoices_details_fk1 foreign key (master_id) references test_invoices_master (id),
constraint test_invoices_details_fk2 foreign key (item_code) references test_items (item_code)
);
commit;
--------------------------------------------------------------------------------------------------------------------------------------------------
insert into test_item_group
(group_code, group_desc)
values
(1, 'Lebensmittel');
insert into test_item_group
(group_code, group_desc)
values
(2, 'Reinigungsmitel');
insert into test_items
(item_code, group_code, item_desc)
values
(1, 1, 'Kartoffel');
insert into test_items
(item_code, group_code, item_desc)
values
(2, 1, 'Fleisch');
insert into test_items
(item_code, group_code, item_desc)
values
(3, 2, 'Shampoo');
insert into test_items
(item_code, group_code, item_desc)
values
(4, 2, 'Seife');
insert into test_invoices_master
(id, invoice_no)
values
(1, 1);
insert into test_invoices_master
(id, invoice_no)
values
(2, 2);
insert into test_invoices_details
(id, master_id, item_code, qty, price)
values
(1, 1, 1, 2, 7);
insert into test_invoices_details
(id, master_id, item_code, qty, price)
values
(2, 1, 3, 2, 7);
insert into test_invoices_details
(id, master_id, item_code, qty, price)
values
(3, 2, 2, 3, 5);
insert into test_invoices_details
(id, master_id, item_code, qty, price)
values
(4, 2, 4, 3, 5);
commit;
*****************************************************************************************************
select r.parent,r.child,r.CHILD_CONSTRAINT,r.PARENT_CONSTRAINT,r.CHILD_CONSTRAINT_TYPE,r.PARENT_CONSTRAINT_TYPE
,nvl(decode(r.CHILD_CONSTRAINT_TYPE,'R',r.CHILD_CONSTRAINT,null),decode(r.PARENT_CONSTRAINT_TYPE,'R',r.PARENT_CONSTRAINT,null))TARGET_FOREIGN
from(
select b.table_name parent,a.table_name child,a.CONSTRAINT_NAME CHILD_CONSTRAINT,b.constraint_name PARENT_CONSTRAINT,a.constraint_type CHILD_CONSTRAINT_TYPE,b.constraint_type PARENT_CONSTRAINT_TYPE
from user_constraints a,user_constraints b
where (b.constraint_name=a.r_constraint_name or a.constraint_name=b.r_constraint_name )
and b.table_name='TEST_INVOICES_MASTER'
)r
;
select r.parent,r.child,r.CHILD_CONSTRAINT,r.PARENT_CONSTRAINT,r.CHILD_CONSTRAINT_TYPE,r.PARENT_CONSTRAINT_TYPE
,nvl(decode(r.CHILD_CONSTRAINT_TYPE,'R',r.CHILD_CONSTRAINT,null),decode(r.PARENT_CONSTRAINT_TYPE,'R',r.PARENT_CONSTRAINT,null))TARGET_FOREIGN
from(
select b.table_name parent,a.table_name child,a.CONSTRAINT_NAME CHILD_CONSTRAINT,b.constraint_name PARENT_CONSTRAINT,a.constraint_type CHILD_CONSTRAINT_TYPE,b.constraint_type PARENT_CONSTRAINT_TYPE
from user_constraints a,user_constraints b
where
a.constraint_name=b.r_constraint_name
and
b.table_name='TEST_INVOICES_DETAILS'
and a.constraint_name not in ('TEST_INVOICES_PK')
)r
;
select r.parent,r.child,r.CHILD_CONSTRAINT,r.PARENT_CONSTRAINT,r.CHILD_CONSTRAINT_TYPE,r.PARENT_CONSTRAINT_TYPE
,nvl(decode(r.CHILD_CONSTRAINT_TYPE,'R',r.CHILD_CONSTRAINT,null),decode(r.PARENT_CONSTRAINT_TYPE,'R',r.PARENT_CONSTRAINT,null))TARGET_FOREIGN
from(
select b.table_name parent,a.table_name child,a.CONSTRAINT_NAME CHILD_CONSTRAINT,b.constraint_name PARENT_CONSTRAINT,a.constraint_type CHILD_CONSTRAINT_TYPE,b.constraint_type PARENT_CONSTRAINT_TYPE
from user_constraints a,user_constraints b
where (b.constraint_name=a.r_constraint_name or a.constraint_name=b.r_constraint_name )
and b.table_name='TEST_ITEMS'
and a.constraint_name not in ('TEST_INVOICES_DETAILS_FK2')
)r
;
select r.parent,r.child,r.CHILD_CONSTRAINT,r.PARENT_CONSTRAINT,r.CHILD_CONSTRAINT_TYPE,r.PARENT_CONSTRAINT_TYPE
,nvl(decode(r.CHILD_CONSTRAINT_TYPE,'R',r.CHILD_CONSTRAINT,null),decode(r.PARENT_CONSTRAINT_TYPE,'R',r.PARENT_CONSTRAINT,null))TARGET_FOREIGN
from(
select b.table_name parent,a.table_name child,a.CONSTRAINT_NAME CHILD_CONSTRAINT,b.constraint_name PARENT_CONSTRAINT,a.constraint_type CHILD_CONSTRAINT_TYPE,b.constraint_type PARENT_CONSTRAINT_TYPE
from user_constraints a,user_constraints b
where (b.constraint_name=a.r_constraint_name or a.constraint_name=b.r_constraint_name )
and b.table_name='TEST_ITEM_GROUP'
and a.constraint_name not in ('TEST_ITEMS_FK1')
)r
;
So I have the tree like this :
TEST_INVOICES_MASTER _____TEST_INVOICES_DETAILS __________TEST_ITEMS _______________TEST_ITEM_GROUP
but of course it could be more than one record (foreign) for example if I added an extra table in details for UOM.
that's why I wanted to build a cursor that takes first time the master table name, then for every details goes throgh its tree untill reach the bottom table of this tree then stop and goes for the next record and so on.
I wrote two procedure but it's like did not worked, I know that this idea has a lot of sides and side effects to think about, but I will applied it for now on simple cases and developed it based on much more complex cases:
create or replace procedure DATEN_NACHSTELLUNG_PROC
(
p_table_name varchar2,
p_error_no out varchar2,
p_error_desc out varchar2
)
is
v_Main_parent varchar2(100);
v_Main_child varchar2(100);
v_Main_child_constraint varchar2(100);
v_Main_parent_constraint varchar2(100);
v_Main_child_constraint_type varchar2(100);
v_Main_parent_constraint_type varchar2(100);
v_Main_target_foreign varchar2(100);
v_last_table varchar2(100);
proc_failure exception;
begin
delete from test_target_tables;
begin
INSERT INTO test_target_tables ( table_name ) VALUES ( p_table_name );
end;
begin
select r.parent,r.child,r.CHILD_CONSTRAINT,r.PARENT_CONSTRAINT,r.CHILD_CONSTRAINT_TYPE,r.PARENT_CONSTRAINT_TYPE
,nvl(decode(r.CHILD_CONSTRAINT_TYPE,'R',r.CHILD_CONSTRAINT,null),decode(r.PARENT_CONSTRAINT_TYPE,'R',r.PARENT_CONSTRAINT,null))TARGET_FOREIGN
into v_Main_parent,v_Main_child,v_Main_child_constraint,v_Main_parent_constraint,v_Main_child_constraint_type,v_Main_parent_constraint_type,v_Main_target_foreign
from(
select b.table_name parent,a.table_name child,a.CONSTRAINT_NAME CHILD_CONSTRAINT,b.constraint_name PARENT_CONSTRAINT,a.constraint_type CHILD_CONSTRAINT_TYPE,b.constraint_type PARENT_CONSTRAINT_TYPE
from user_constraints a,user_constraints b
where (b.constraint_name=a.r_constraint_name or a.constraint_name=b.r_constraint_name )
and b.table_name=p_table_name
)r
;
end;
DA_NACHSTELLUNG_DETAILS_PROC(v_Main_child,v_main_parent_constraint,p_error_no,p_error_desc);
if p_error_no is not null or p_error_desc is not null then
raise proc_failure;
end if;
commit;
exception
when proc_failure then
null;
when others then
P_ERROR_NO := '-1';
p_error_desc :='Error in DATEN_NACHSTELLUNG_PROC ###END'||sqlerrm;
rollback;
end;
the second one :
create or replace procedure DA_NACHSTELLUNG_DETAILS_PROC
(
p_table_name varchar2,
p_constraint varchar2,
p_error_no out varchar2,
p_error_desc out varchar2
)
is
cursor get_details ( p_cur_table_name varchar2, p_cur_constraint varchar2) is
select r.parent,r.child,r.CHILD_CONSTRAINT,r.PARENT_CONSTRAINT,r.CHILD_CONSTRAINT_TYPE,r.PARENT_CONSTRAINT_TYPE
,nvl(decode(r.CHILD_CONSTRAINT_TYPE,'R',r.CHILD_CONSTRAINT,null),decode(r.PARENT_CONSTRAINT_TYPE,'R',r.PARENT_CONSTRAINT,null))TARGET_FOREIGN
from(
select b.table_name parent,a.table_name child,a.CONSTRAINT_NAME CHILD_CONSTRAINT,b.constraint_name PARENT_CONSTRAINT,a.constraint_type CHILD_CONSTRAINT_TYPE,b.constraint_type PARENT_CONSTRAINT_TYPE
from user_constraints a,user_constraints b
where
a.constraint_name=b.r_constraint_name
and
b.table_name=p_cur_table_name
and a.constraint_name != p_cur_constraint
)r
;
v_intern_table_name varchar2(500);
v_intern_constraint varchar2(500);
v_cursor_fired number:=0;
proc_failure exception;
begin
if v_cursor_fired=0 then
v_intern_table_name:=p_table_name;
v_intern_constraint:=p_constraint;
end if;
/*while v_intern_table_name!=null
for rec in get_details loop
v_cursor_fired:=1;
INSERT INTO test_target_tables ( table_name ) VALUES ( rec.parent );
DATEN_NACHSTELLUNG_DETAILS_PROC(rec.child,rec.parent_constraint,p_error_no,p_error_desc);
end loop;*/
BEGIN
WHILE v_intern_table_name is not null LOOP
FOR rec in get_details(v_intern_table_name,v_intern_constraint) LOOP
v_cursor_fired:=1;
INSERT INTO test_target_tables ( table_name ) VALUES ( rec.parent );
v_intern_table_name:=rec.child;
v_intern_constraint:=rec.parent_constraint;
END LOOP; -- for loop
INSERT INTO test_target_tables ( table_name ) VALUES ( v_intern_table_name );
v_intern_table_name:=null;
v_intern_constraint:=null;
END LOOP; -- while loop
END;
/*if v_inserted!=1 then
begin
v_inserted:=1;
INSERT INTO test_target_tables ( table_name ) VALUES ( p_table_name );
end;
end if;*/
commit;
exception
when proc_failure then
null;
when others then
P_ERROR_NO := '-1';
p_error_desc :='Error in DATEN_NACHSTELLUNG_PROC ###END'||sqlerrm;
rollback;
end;
*** U P D A T E :
As description of my idea please see the query and result , so this result I need only to save like a serial for 1 for the master table and every level a detail and so until I reached the table that has no detail(The Root), which will no have any record from the last execution of the cursor but we can get it's value as a parent from the previous execution of the cursor.
select r.parent,r.child,r.CHILD_CONSTRAINT,r.PARENT_CONSTRAINT,r.CHILD_CONSTRAINT_TYPE,r.PARENT_CONSTRAINT_TYPE
,nvl(decode(r.CHILD_CONSTRAINT_TYPE,'R',r.CHILD_CONSTRAINT,null),decode(r.PARENT_CONSTRAINT_TYPE,'R',r.PARENT_CONSTRAINT,null))TARGET_FOREIGN
from(
select b.table_name parent,a.table_name child,a.CONSTRAINT_NAME CHILD_CONSTRAINT,b.constraint_name PARENT_CONSTRAINT,a.constraint_type CHILD_CONSTRAINT_TYPE,b.constraint_type PARENT_CONSTRAINT_TYPE
from user_constraints a,user_constraints b
where (b.constraint_name=a.r_constraint_name or a.constraint_name=b.r_constraint_name )
and b.table_name='TEST_INVOICES_MASTER'
)r
union all
select r.parent,r.child,r.CHILD_CONSTRAINT,r.PARENT_CONSTRAINT,r.CHILD_CONSTRAINT_TYPE,r.PARENT_CONSTRAINT_TYPE
,nvl(decode(r.CHILD_CONSTRAINT_TYPE,'R',r.CHILD_CONSTRAINT,null),decode(r.PARENT_CONSTRAINT_TYPE,'R',r.PARENT_CONSTRAINT,null))TARGET_FOREIGN
from(
select b.table_name parent,a.table_name child,a.CONSTRAINT_NAME CHILD_CONSTRAINT,b.constraint_name PARENT_CONSTRAINT,a.constraint_type CHILD_CONSTRAINT_TYPE,b.constraint_type PARENT_CONSTRAINT_TYPE
from user_constraints a,user_constraints b
where
a.constraint_name=b.r_constraint_name
and
b.table_name='TEST_INVOICES_DETAILS'
and a.constraint_name not in ('TEST_INVOICES_PK')
)r
union all
select r.parent,r.child,r.CHILD_CONSTRAINT,r.PARENT_CONSTRAINT,r.CHILD_CONSTRAINT_TYPE,r.PARENT_CONSTRAINT_TYPE
,nvl(decode(r.CHILD_CONSTRAINT_TYPE,'R',r.CHILD_CONSTRAINT,null),decode(r.PARENT_CONSTRAINT_TYPE,'R',r.PARENT_CONSTRAINT,null))TARGET_FOREIGN
from(
select b.table_name parent,a.table_name child,a.CONSTRAINT_NAME CHILD_CONSTRAINT,b.constraint_name PARENT_CONSTRAINT,a.constraint_type CHILD_CONSTRAINT_TYPE,b.constraint_type PARENT_CONSTRAINT_TYPE
from user_constraints a,user_constraints b
where (b.constraint_name=a.r_constraint_name or a.constraint_name=b.r_constraint_name )
and b.table_name='TEST_ITEMS'
and a.constraint_name not in ('TEST_INVOICES_DETAILS_FK2')
)r
union all
select r.parent,r.child,r.CHILD_CONSTRAINT,r.PARENT_CONSTRAINT,r.CHILD_CONSTRAINT_TYPE,r.PARENT_CONSTRAINT_TYPE
,nvl(decode(r.CHILD_CONSTRAINT_TYPE,'R',r.CHILD_CONSTRAINT,null),decode(r.PARENT_CONSTRAINT_TYPE,'R',r.PARENT_CONSTRAINT,null))TARGET_FOREIGN
from(
select b.table_name parent,a.table_name child,a.CONSTRAINT_NAME CHILD_CONSTRAINT,b.constraint_name PARENT_CONSTRAINT,a.constraint_type CHILD_CONSTRAINT_TYPE,b.constraint_type PARENT_CONSTRAINT_TYPE
from user_constraints a,user_constraints b
where (b.constraint_name=a.r_constraint_name or a.constraint_name=b.r_constraint_name )
and b.table_name='TEST_ITEM_GROUP'
and a.constraint_name not in ('TEST_ITEMS_FK1')
)r
;
