How to make OUT parameters optional and assign to them default values

How to make OUT parameters optional ?

OUT and IN OUT parameters of procedures can’t be optional – they can’t be assigned with any default values like NULL, ‘AAA’, 10 or something like that:

...
procedure out_p (r out number := null) … /* Wrong syntax*/
...

But sometimes it can be needed. The real question from one of the forums was:

“I am having a procedure with multiple out parameters. At the time of calling the procedure all the OUT parameters may not be required. I need to skip a few of the OUT parameters at the time of calling.

I know it is possible to make IN parameters in a PL/SQL procedure optional. But how to do the same for OUT parameters?

I do not want to create overloaded procedures to achieve it. Because in that case the number of procedures should be as many number of combinations of out parameters. That would lead to around 10/15 overloaded procedures.”

One of the work-around is to combine OUT parameters
into the record and get one such OUT parameter each time
instead of variable number of OUT parameters. We can control what
particular fields of record should have values:

SQL> create or replace package test_pkg
  2  is
  3    type e_rec is record (
  4     ename emp.ename%type := 'KING',
  5     sal emp.sal%type := 5000,
  6     empno emp.empno%type := 7369
  7    );
  8    procedure get_rec(idx in number, rc out e_rec);
  9  end;
 10  /

Package created.

SQL> create or replace package body test_pkg
  2  is
  3    procedure get_rec(idx in number, rc out e_rec)
  4    is
  5    begin
  6      if idx = 1 then
  7         rc.ename := 'SMITH';
  8         rc.sal := NULL;
  9         rc.empno := NULL; 
 10      elsif idx = 2 then
 11         rc.sal := 1000;
 12         rc.ename := NULL;
 13         rc.empno := NULL;
 14      else
 15         rc.empno := 7777;
 16         rc.sal := NULL;
 17         rc.ename := NULL;
 18      end if;
 19    end;
 20  end;
 21  /

Package body created.

In the example above we replaced three potential OUT parameters – ename, sal, empno - with one record which combines all three. So:

SQL> declare
  2   a test_pkg.e_rec;
  3  begin
  4   test_pkg.get_rec(1,a);
  5   dbms_output.put_line('ename = ' || a.ename || ', sal = ' || a.sal ||
  6   ', empno = ' || a.empno);
  7   test_pkg.get_rec(2,a);
  8   dbms_output.put_line('ename = ' || a.ename || ', sal = ' || a.sal ||
  9   ', empno = ' || a.empno);
 10   test_pkg.get_rec(3,a);
 11   dbms_output.put_line('ename = ' || a.ename || ', sal = ' || a.sal ||
 12   ', empno = ' || a.empno);
 13   test_pkg.get_rec(4,a);
 14   dbms_output.put_line('ename = ' || a.ename || ', sal = ' || a.sal ||
 15   ', empno = ' || a.empno);
 16  end;
 17  /
ename = SMITH, sal = , empno =
ename = , sal = 1000, empno =
ename = , sal = , empno = 7777
ename = KING, sal = 5000, empno = 7369

PL/SQL procedure successfully completed.

As we can see, we also can assign default values to OUT parameters there using record type declaration syntax.

What if we want to keep default values only for a part of parameters ? In this case we can use one IN and one OUT parameter:

SQL> create or replace package test_pkg
  2  is
  3    type e_rec is record (
  4     ename emp.ename%type := 'KING',
  5     sal emp.sal%type := 5000,
  6     empno emp.empno%type := 7369
  7    );
  8    procedure get_rec(idx in number, rc out e_rec);
  9    procedure get_rec(idx in number, rc out e_rec, irc in e_rec);
 10  end;
 11  /

Package created.

SQL> create or replace package body test_pkg
  2  is
  3    procedure get_rec_inn(idx in number, rc in out e_rec)
  4    is
  5    begin
  6      if idx = 1 then
  7         rc.ename := 'SMITH';
  8         rc.sal := NULL;
  9         rc.empno := NULL;
 10      elsif idx = 2 then
 11         rc.sal := 1000;
 12         rc.ename := NULL;
 13         rc.empno := NULL;
 14      elsif idx = 3 then
 15         rc.empno := 7777;
 16         rc.sal := NULL;
 17         rc.ename := NULL;
 18      end if;
 19    end;
 20  
 21    procedure get_rec(idx in number, rc out e_rec)
 22    is
 23    begin
 24      get_rec_inn(idx,rc);
 25    end;
 26  
 27  
 28    procedure get_rec(idx in number, rc out e_rec, irc in e_rec)
 29    is
 30    begin
 31      rc := irc;
 32      get_rec_inn(idx,rc);
 33    end;
 34  
 35  end;
 36  /

Package body created.

And now:

SQL> declare
  2   a test_pkg.e_rec;
  3  begin
  4   
  5   test_pkg.get_rec(4,a); 
  6   dbms_output.put_line('ename = ' || a.ename || ', sal = ' || a.sal ||
  7   ', empno = ' || a.empno);
  8  
  9   a.ename := 'ALLEN';
 10  
 11   test_pkg.get_rec(4,a,a);
 12   dbms_output.put_line('ename = ' || a.ename || ', sal = ' || a.sal ||
 13   ', empno = ' || a.empno);
 14  
 15  end;
 16  /
ename = KING, sal = 5000, empno = 7369
ename = ALLEN, sal = 5000, empno = 7369

PL/SQL procedure successfully completed.