Learning is not a spectator sport

May 13, 2013

Default null for collection parameter

Filed under: Uncategorized — connormcdonald @ 5:41 pm

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.
About these ads

4 Comments »

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

    Comment by Toon — May 13, 2013 @ 7:35 pm

  2. 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.

    Comment by connormcdonald — May 13, 2013 @ 7:41 pm

    • 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

      Comment by Sayan Malakshinov — May 21, 2013 @ 4:01 am

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

    Comment by Toon — May 13, 2013 @ 8:03 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The WordPress Classic Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 78 other followers

%d bloggers like this: