I encountered this "bug" in some code I got and was able to reduce it to this example ( I'll only give the package body, the spec is simply the procedure GO)
package body asl_test
is
type gr_rec is record ( id number , kol varchar2(1) );
type gt_tab is table of gr_rec index by binary_integer;
g_tab gt_tab := gt_tab();
g_const varchar2(10);
procedure modify_record
( p_in in out gr_rec --> this breaks the mutability of the global collection
)
is
begin
-- This works...
g_const := 2;
/*
But the IN OUT ignores this modification
Original code deleted the entry from the collection
but the entry stayed.
*/
g_tab(p_in.id).kol := 'J';
end;
procedure modify_collection
is
begin
for i in g_tab.first .. g_tab.last
loop
modify_record(g_tab(i));
end loop;
end;
procedure go
is
begin
g_const := 1;
g_tab(1).id := 1; g_tab(1).kol := 'N';
g_tab(2).id := 2; g_tab(2).kol := 'N';
--
modify_collection();
--
dbms_output.put_line('The collection is not modified!');
for i in g_tab.first .. g_tab.last
loop
dbms_output.put_line(g_tab(i).kol);
end loop;
dbms_output.put_line('variable is correctly modified to 2: '||g_const);
end;
end;
So I defined a global collection in the body and filled it with some data.
Then iterated over the collection and modified it per record by calling a procedure with an IN OUT parameter (they had some reason to do this).
The modifications of the global variable are kept after leaving the procedure, but the modifications to the collection are lost!
But when I alter the IN OUT to an IN, the modifications to the collection are kept.
So when changing an input-variable from
by reference to
by value, an unrelated variable in my code becomes immutable.
Can you explain this behaviour?
The issue here is the code doesn't assign a value to the IN OUT parameter. The chain of events is:
- Pass the VALUE for the parameter IN
- Set the global variable
- Pass the VALUE for the parameter back OUT
Because the actual parameter is a global variable, this happens:
- Pass the VALUE for the parameter IN
- Set the global variable
- Pass the VALUE for the parameter back OUT - OVERWRITING the previous assignment to the global
A simpler example shows this:
DECLARE
n NUMBER := 10;
PROCEDURE p (
n1 IN OUT NUMBER
) IS
BEGIN
DBMS_OUTPUT.put_line('Global ' || n); -- global value is 10
DBMS_OUTPUT.put_line('Actual ' || n1); -- actual parameter value is 10
n := -99;
DBMS_OUTPUT.put_line('Global ' || n); -- global value is -99
DBMS_OUTPUT.put_line('Actual ' || n1); -- actual parameter value is still 10
-- this value is copied back to the global when this procedure exits!
END;
BEGIN
p(n);
DBMS_OUTPUT.put_line(n);
END;
/
Global 10
Actual 10
Global -99
Actual 10
10To avoid this you need to assign a value to the parameter in the procedure:
DECLARE
n NUMBER := 10;
PROCEDURE p (
n1 IN OUT NUMBER
) IS
BEGIN
DBMS_OUTPUT.put_line('Global ' || n); -- global value is 10
DBMS_OUTPUT.put_line('Actual ' || n1); -- actual parameter value is 10
n1 := -99;
DBMS_OUTPUT.put_line('Global ' || n); -- global value is 10
DBMS_OUTPUT.put_line('Actual ' || n1); -- actual parameter value is still -99
-- this is copied back over the global value when this procedure exits!
END;
BEGIN
p(n);
DBMS_OUTPUT.put_line(n);
END;
/
Global 10
Actual 10
Global 10
Actual -99
-99Change the assignment in modify_collection to:
p_in.kol := 'J';
And you'll get the behaviour you want.
But passing globals as IN OUT parameters is confusing - it kinda defeats the point of making it a global.
It'd be better to pass the index of the array entry you want to change as an IN parameter:
procedure modify_record ( p_in in number )
is
begin
g_const := 2;
g_tab(p_in).kol := 'J';
end;
procedure modify_collection
is
begin
for i in g_tab.first .. g_tab.last
loop
modify_record(i);
end loop;
end;