SQL & PL/SQL

Hi,

Looking for suggestions for the following.

I have a procedure with 4 table types.

They are in a hierarchical order so that 4 is the child of 3, 3 is the child of 2 and 2 is the child of 1.


One of the columns of table 1 is a table type holding all the child records from table 2,
One of the columns of table 2 is a table type holding all the child records from table 3,
One of the columns of table 3 is a table type holding all the child records from table 4,

I have 4 cursors that pull out all of the data to populate each of my 4 table types.

I am trying to figure out how to nest my fetches to populate all of my table types to create a single object that hold all of my data.

It’s a table of a table of a table of a table.

Column 3 of table 1 holds table 2
Column 3 of table 2 holds table 3
Column 3 of table 3 holds table 4


I’ve tried creating my procedure like this (pseudo code)


declare

tab_1 tab_1_type;
tab_2 tab_2_type;
tab_3 tab_3_type;
tab_4 tab_4_type;


cursor get_tab_1 is
select col1, col2, tab_2 – added the table type for the child records
from data_table_1

cursor get_tab_2(tab_1_pk in number) is
select col1, col2, tab_3 – added the table type for the child records
from data_table_2
where tab_2_fk = tab_1_pk

cursor get_tab_3(tab_2_pk in number) is
select col1, col2, tab_4 – added the table type for the child records
from data_table_3
where tab_3_fk = tab_2_pk


cursor get_tab_4(tab_3_pk in number) is
select col1, col2, col3
from data_table_4
where tab_4_fk = tab_3_pk


begin

open get_tab_1;
loop

open get_tab_2
loop

open get_tab_3
loop

open get_tab_4(tab_3_pk);
bulk collect get_tab_4 into tab_4;
close get_tab_4;

fetch get_tab_3 into tab_3;
tab_4:= null;
exit when get_tab_3 %notfound;
end loop;
close get_tab_3;

fetch get_tab_2 into tab_2;
tab_3:= null;
exit when get_tab_2 %notfound;
end loop;
close get_tab_2;

fetch get_tab_1 into tab_1;
tab_3:= null;
exit when get_tab_1 %notfound;
end loop;
close get_tab_1;

l_return := tab_1;

end;

The above won’t work because once the cursor is opened the child tables will be assigned values of null before they have had a chance to populate from the nested fetches.

It’s almost as if I need to execute the fetch and update the extra columns afterwards somehow, something like:

fetch get_tab_3 into tab_3;
tab_3.col3 := tab_4;
tab_4 := null;
end loop;
close get_tab_3;

can I do that?

Also the above seems very cumbersome. If there is a more elegant way of building up this object I’d like to hear it.

Cheers

Yog