I've noticed strange side-effect when working with associative arrays in pl/sql. Basically, it appearts, that when element of the array is passed to procedure as "in out nocopy", then after procedure finishes, Oracle copies possibly updated element back into associative array. The problem is, Oracle uses the variable that contained key before the procedure was called. Everything is fine untill procedure also changes that key.
I've provided the code as LiveSQL Link, but just in case include it also here:
--%<---%<---%<---
declare
type tr_zs_info is record(
von varchar2(10),
bis varchar2(10)
);
type th_zs_set is table of tr_zs_info index by varchar2(10);
l_key varchar2(10);
l_other varchar2(10);
l_hash th_zs_set;
procedure split_period(
pl_el in out nocopy tr_zs_info,
p_at varchar2,
pl_key in out nocopy varchar2,
pl_dst in out nocopy th_zs_set
) is
l_obj tr_zs_info;
begin
dbms_output.put_line('start of split von ' || pl_el.von || ' bis ' || pl_el.bis);
l_obj.von := p_at;
l_obj.bis := pl_el.bis;
pl_el.bis := p_at;
pl_dst(p_at) := l_obj;
pl_key := p_at;
dbms_output.put_line('end of split '
|| p_at || ' von ' || pl_dst(p_at).von || ' bis ' || pl_dst(p_at).bis);
end split_period; -- }}}
begin
l_key := '2017-01-01';
l_hash(l_key).von := l_key;
l_hash(l_key).bis := '2017-02-01';
l_other := l_key;
split_period(l_hash(l_key), '2017-01-15', l_key, l_hash);
dbms_output.put_line('after split '
|| l_key || ' von ' || l_hash(l_key).von || ' bis ' || l_hash(l_key).bis);
dbms_output.put_line('original '
|| l_other || ' von ' || l_hash(l_other).von || ' bis ' || l_hash(l_other).bis);
end;
--%<---%<---%<---
The above code produces
--%<---%<---%<---
start of split von 2017-01-01 bis 2017-02-01
end of split 2017-01-15 von 2017-01-15 bis 2017-02-01
after split 2017-01-15 von 2017-01-01 bis 2017-01-15
original 2017-01-01 von 2017-01-01 bis 2017-02-01
--%<---%<---%<---
So after procedure finishes, it is expected that record with key '2017-01-01' has "bis" equal to 2017-01-15, and new record with key '2017-01-15' shall have "von" equal to 2017-01-15. And it is so before procedure ends. After the end of procedure the records are exchanged.
My question is, is this a bug, or a feature? In other words shall that get fixed, or documented?
That example is not mentioned, but the docs do go into a *lot* of detail (and warnings) about the passing of parameters by reference and value, and the implications of nocopy and parameter aliasing.
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/plsql-subprograms.html I changed your proc a little to make it easier for readers to comprehend to demonstrate the differences (as both example and warning)
SQL> declare
2 type my_rec is record(
3 dateidx varchar2(20),
4 data varchar2(20)
5 );
6 type my_array is table of my_rec index by varchar2(20);
7
8 l_key varchar2(10);
9 l_array my_array;
10
11 procedure print is
12 x varchar2(20);
13 begin
14 dbms_output.put_line('-------------------------');
15 dbms_output.put_line('l_key='||l_key);
16 x := l_array.first;
17 while x is not null
18 loop
19 dbms_output.put_line('IDX:'||x||' DATEIDX:'||l_array(x).dateidx||', DATA:'||l_array(x).data);
20 x := l_array.next(x);
21 end loop;
22 end;
23
24 procedure split_period(
25 pl_el in out nocopy my_rec,
26 p_at_date varchar2,
27 pl_key in out nocopy varchar2,
28 pl_dst in out nocopy my_array
29 ) is
30 l_obj my_rec;
31 begin
32 print;
33 --
34 -- create new entry with the data from existing key
35 --
36 l_obj.dateidx := p_at_date;
37 l_obj.data := pl_el.data;
38 --
39 -- update existing key with new data
40 --
41 pl_el.data := 'SOME_MORE_DATA';
42
43 --
44 -- insert new entry into the array
45 --
46 pl_dst(p_at_date) := l_obj;
47 --
48 -- last used key becomes this entry
49 --
50 pl_key := p_at_date;
51 print;
52 end split_period;
53 begin
54 l_key := '2017-01-01';
55 l_array(l_key).dateidx := l_key;
56 l_array(l_key).data := 'SOME_DATA';
57 split_period(l_array(l_key), '2017-01-15', l_key, l_array);
58 print;
59 end;
60 /
-------------------------
l_key=2017-01-01
IDX:2017-01-01 DATEIDX:2017-01-01, DATA:SOME_DATA
-------------------------
l_key=2017-01-15
IDX:2017-01-01 DATEIDX:2017-01-01, DATA:SOME_DATA
IDX:2017-01-15 DATEIDX:2017-01-15, DATA:SOME_DATA
-------------------------
l_key=2017-01-15
IDX:2017-01-01 DATEIDX:2017-01-01, DATA:SOME_DATA
IDX:2017-01-15 DATEIDX:2017-01-01, DATA:SOME_MORE_DATA
PL/SQL procedure successfully completed.
SQL>
SQL> declare
2 type my_rec is record(
3 dateidx varchar2(20),
4 data varchar2(20)
5 );
6 type my_array is table of my_rec index by varchar2(20);
7
8 l_key varchar2(10);
9 l_array my_array;
10
11 procedure print is
12 x varchar2(20);
13 begin
14 dbms_output.put_line('-------------------------');
15 dbms_output.put_line('l_key='||l_key);
16 x := l_array.first;
17 while x is not null
18 loop
19 dbms_output.put_line('IDX:'||x||' DATEIDX:'||l_array(x).dateidx||', DATA:'||l_array(x).data);
20 x := l_array.next(x);
21 end loop;
22 end;
23
24 procedure split_period(
25 pl_el in out my_rec,
26 p_at_date varchar2,
27 pl_key in out varchar2,
28 pl_dst in out my_array
29 ) is
30 l_obj my_rec;
31 begin
32 print;
33 --
34 -- create new entry with the data from existing key
35 --
36 l_obj.dateidx := p_at_date;
37 l_obj.data := pl_el.data;
38 --
39 -- update existing key with new data
40 --
41 pl_el.data := 'SOME_MORE_DATA';
42
43 --
44 -- insert new entry into the array
45 --
46 pl_dst(p_at_date) := l_obj;
47 --
48 -- last used key becomes this entry
49 --
50 pl_key := p_at_date;
51 print;
52 end split_period;
53 begin
54 l_key := '2017-01-01';
55 l_array(l_key).dateidx := l_key;
56 l_array(l_key).data := 'SOME_DATA';
57 split_period(l_array(l_key), '2017-01-15', l_key, l_array);
58 print;
59 end;
60 /
-------------------------
l_key=2017-01-01
IDX:2017-01-01 DATEIDX:2017-01-01, DATA:SOME_DATA
-------------------------
l_key=2017-01-01
IDX:2017-01-01 DATEIDX:2017-01-01, DATA:SOME_DATA
-------------------------
l_key=2017-01-15
IDX:2017-01-01 DATEIDX:2017-01-01, DATA:SOME_DATA
IDX:2017-01-15 DATEIDX:2017-01-15, DATA:SOME_DATA
PL/SQL procedure successfully completed.