Hi - i would like to create a package which stores collections with nested tables, functions (or procedures) should provide access to the nested tables.
I ran into some performance problems and came up to the conclusion that the memory structures (the collections / nested tables) are passed by value (so copied over and over again) instead of passing them.
So i tried to find out if it is simply possible to pass a collection via reference instead of creating new memory duplicates - here is what i tried:
declare
type t is record
(
x number,
y varchar2(100)
);
type t_tab is table of t;
type tt_tab is table of t_tab index by varchar2(100);
tt tt_tab;
t_elem t;
t_tab_elem t_tab;
n_x number;
procedure ttttx(v_index_in in varchar2, n_index_in in number, t_ret_inout out nocopy number) is
begin
t_ret_inout := tt(v_index_in)(n_index_in).x;
end;
procedure tttt(v_index_in in varchar2, n_index_in in number, t_ret_inout out nocopy t) is
begin
t_ret_inout := tt(v_index_in)(n_index_in);
end;
procedure ttt(v_index_in in varchar2, t_ret_inout out nocopy t_tab) is
begin
t_ret_inout := tt(v_index_in);
end;
begin
tt('A') := new t_tab();
tt('A').extend(1);
tt('A')(1).x := 1;
tt('A')(1).y := 'asdf';
ttt('A', t_tab_elem);
t_tab_elem(1).x := 99;
dbms_output.put_line(t_tab_elem(1).x);
dbms_output.put_line(tt('A')(1).x);
tttt('A', 1, t_elem);
t_elem.x := 100;
dbms_output.put_line(t_elem.x);
dbms_output.put_line(tt('A')(1).x);
ttttx('A', 1, n_x);
n_x := 101;
dbms_output.put_line(n_x);
dbms_output.put_line(tt('A')(1).x);
end;
And unfortunatly the result is
99
1
100
1
101
1
... so there is not a single reference, obviously those are all copies - did i miss something?
thanks in advance
best regards
Werner