Reading from a write-only parameter (OUT) in pl / sql
When I tried to write a read-only parameter (IN) to a function, Oracle complains about an error. But this is not the case when you are reading a write-only parameter (OUT) of a function. Oracle silently resolves this without any error. What is the reason for this behavior? The following code is executed without any assignments occurring to the "so" variable:
create or replace function foo(a OUT number) return number
is
so number;
begin
so := a; --no assignment happens here
a := 42;
dbms_output.put_line('HiYA there');
dbms_output.put_line('VAlue:' || so);
return 5;
end;
/
declare
somevar number;
a number := 6;
begin
dbms_output.put_line('Before a:'|| a);
somevar := foo(a);
dbms_output.put_line('After a:' || a);
end;
/
Here's the output I got:
Before a:6
HiYA there
VAlue:
After a:42
a source to share
Reading from an OUT parameter is allowed: you could write things to your OUT parameter at the beginning of the procedure, and you might need to read the value it contains before returning, this is not an error.
What happens here is that since it is an OUT parameter and not an IN OUT parameter, the value is a
not passed to the function foo
, so at the beginning of the procedure, the OUT parameter a
contains the value NULL
. You can check it out by commenting out the line a := 42;
:
SQL> create or replace function foo(a OUT number) return number
2 is
3 so number;
4 begin
5 so := a; --no assignment happens here
6 /*a := 42;*/
7 dbms_output.put_line('HiYA there');
8 dbms_output.put_line('VAlue:' || so);
9 return 5;
10 end;
11 /
Function created
SQL> declare
2 somevar number;
3 a number := 6;
4 begin
5 dbms_output.put_line('Before a:'|| a);
6 somevar := foo(a);
7 dbms_output.put_line('After a:' || a);
8 end;
9 /
Before a:6
HiYA there
VAlue:
After a:
^^ as you can see an OUT parameter is effectively "NULLed" at the
beginning of a call
a source to share