René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

August 13, 2006: On out parameters

I have recently stumbled upon something, which, after some thinking about, makes partially sense. The corpus delicti is a procedure that has an out parameter such as the following one.
create procedure out_param (param_a out number) 
is begin
  dbms_output.put_line('  param_a: ' || param_a);
  param_a := 7;
end out_param;
/
If I now call the procedure with the following block, what do you think will be printed?
declare
  x number;
begin
  x := 2;
  out_param(x);
  dbms_output.put_line('  x: ' || x);
end;
/
I had thought that the value of param_a within out_param would be 2 until it is assigned 7. But no, out_param is null until it is assigned a value:
  param_a:
  x: 7
Things change however if the parameter is declared in out:
create procedure in_out_param (param_a in out number) 
is begin
  dbms_output.put_line('  param_a: ' || param_a);
  param_a := 7;
end in_out_param;
/
declare
  x number;
begin
  x := 2;
  in_out_param(x);
  dbms_output.put_line('  x: ' || x);
end;
/
  param_a: 2
  x: 7

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.