How to we pass default value as null to Associative Array in Procedure

oracleplsql

How can we pass NULL as a default value to an associative array?

I have tried using

DEFAULT cast (null as t_test)

Here t_test is an associative array.

Best Answer

Casting it as NULL should work fine:

DECLARE
  TYPE t_test IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;

  PROCEDURE test_me ( in_test IN t_test DEFAULT CAST( NULL AS t_test ) )
  AS
  BEGIN
    dbms_output.put_line( in_test.COUNT );
  END test_me;

BEGIN
  test_me();
END;

Related Solutions

Initializing Associative Array in PL/SQL

Updated answer for 18c and beyond

In 18c Oracle has introduced qualified expressions that can also be used to initialize an associative array. Example from the documentation:

DECLARE
  TYPE t_aa IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
  v_aa1 t_aa := t_aa(1=>FALSE,
                     2=>TRUE,
                     3=>NULL);
BEGIN
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(1)));
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(2)));
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(3)));
END;

See also chapter Qualified Expressions for Associative Arrays from Easy Initializing for Records and Arrays by Steven Feuerstein

Original answer upto 12c

No - there is no a short-cut syntax to initialize an associative array. Unlike varrays and nested tables associative arrays do not have collection constructors.

Instead you should write your own initializer function. See an example from Declaring Associative Array Constants.

Retrieve Indices of Associative Array as Collection

Is there some operator or function that can give me the indices of the array

No, you have to loop through the associative array:

declare 
       type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER;
       type keys_t is table of PLS_INTEGER;
       a_arr a_arr_t;
       keys_ keys_t := keys_t();
       l_index integer;
begin
      a_arr(1)   := 2;
      a_arr(10)  := 4;
      a_arr(100) := 6;

      l_index := a_arr.first;
      while (l_index is not null)
      loop
          keys_.extend();
          keys_(keys_.count):=l_index;
          dbms_output.put_line(keys_(keys_.count));
          l_index := a_arr.next(l_index);
      end loop;
end;
/

/*
1
10
100
*/

Best Answer

Related Solutions

Initializing Associative Array in PL/SQL

Retrieve Indices of Associative Array as Collection

Related Question