Default null for collection parameter

I’ve got an existing package called DEMO as below

SQL> create or replace package demo is
  2     -- used to pass list of numbers
  3     TYPE t_num_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  4
  5     PROCEDURE p(
  6       p_list1               IN     t_num_list
  7     );
  8  end;
  9  /

Package created.

but what I’d like to do is add another collection parameter to it, whilst keeping backward compatibility

I could use a overloaded version, or I can default that second parameter to null. Lets explore the second option:

SQL> create or replace package demo is
  2     -- used to pass list of numbers
  3     TYPE t_num_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  4
  5     PROCEDURE p(
  6       p_list1               IN     t_num_list,
  7       p_list2               IN     t_num_list default null
  8     );
  9  end;
 10  /

Warning: Package created with compilation errors.

SQL> sho err
Errors for PACKAGE DEMO:

LINE/COL ERROR
-------- ---------------------------------------------------------------
5/4      PL/SQL: Declaration ignored
7/54     PLS-00382: expression is of wrong type

Well that’s no good. What we need to do is ensure that the types are consistent, like this:

SQL> create or replace package demo is
  2     -- used to pass list of numbers
  3     TYPE t_num_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  4
  5     PROCEDURE p(
  6       p_list1               IN     t_num_list,
  7       p_list2               IN     t_num_list default cast(null as t_num_list)
  8     );
  9  end;
 10  /

Package created.

But just remember, in doing so, when you reference the parameter inside your package body, it is no longer null in the ‘conventional’ sense, as the following little example demonstrates

SQL> create or replace package body  demo is
  2
  3     PROCEDURE p(
  4       p_list1               IN     t_num_list,
  5       p_list2               IN     t_num_list default cast(null as t_num_list)
  6     ) is
  7     begin
  8       if p_list2 is null then
  9          dbms_output.put_line('I am null');
 10       end if;
 11
 12       if p_list2.count = 0 then
 13          dbms_output.put_line('I am empty');
 14       end if;
 15    end;
 16  end;
 17  /

Package body created.


SQL> set serverout on

SQL> declare
  2     x demo.t_num_list;
  3  begin
  4    demo.p(x,x);
  5  end;
  6  /
I am empty

PL/SQL procedure successfully completed.

5 responses to “Default null for collection parameter”

Toon

May 13, 2013 at 7:35 pm

And what happens if you’d called “demo.p(x);” instead?

connormcdonald

May 13, 2013 at 7:41 pm

LOL….of course, that was the whole point of the post !

SQL> declare
2 x demo.t_num_list;
3 begin
4 demo.p(x);
5 end;
6 /
I am empty

PL/SQL procedure successfully completed.

1. Sayan Malakshinov
  
  May 21, 2013 at 4:01 am
  It is very simple: associative arrays can’t be nulled.
  Just try:
```
declare
   TYPE t_num_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   v t_num_list;
begin
   dbms_output.put_line(v.count);
end;
/
```
  Regards,
  Sayan Malakshinov
Toon

May 13, 2013 at 8:03 pm

So the unassigned and the (casted) NULL assigned cases, behave the same. And can you ever meaningfully do a ‘[collection variable] is NULL’ predicate?

steven feuerstein

August 12, 2016 at 9:22 pm

@Toon, not so far as I can tell. You get the same behavior with nested tables and varray. That is, either you do not call the constructor in which case you get an “uninitialized” error, or you initialize and then the count is 0.