Posted by 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.
Learning is not a spectator sport 
And what happens if you’d called “demo.p(x);” instead?
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.
It is very simple: associative arrays can’t be nulled.
Just try:
Regards,
Sayan Malakshinov
So the unassigned and the (casted) NULL assigned cases, behave the same. And can you ever meaningfully do a ‘[collection variable] is NULL’ predicate?
@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.