Retrieve Indices of Associative Array as Collection

arrayoracleplsql

In PL/SQL, suppose I have some associative array defined as follows:

declare 
       type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER;
       a_arr a_arr_t;

I then, sparsely, populate the array as follows:

begin
      a_arr(1)   := 2;
      a_arr(10)  := 4;
      a_arr(100) := 6;
end;

Is there some operator or function that can give me the indices of the array, (1,10,100) as a collection of some sort, something like indices of in a forall statement?

Best Answer

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
*/

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.

FORALL on Nested Collections

It is possible to do this in a single FORALL statement, provided you create the nested tables as SQL objects. This allows you to reference them as tables with the SQL. You can then use INSERT ALL to do a multi-table insert:

create or replace type t_inner_tab_t as table of varchar2(30); 
/

create or replace type outer_tab_t as object (
  num number,
  vals  t_inner_tab_t
);
/

create or replace type t_outer_tab_t is table of outer_tab_t;
/

declare
  inner_tab t_inner_tab_t;
  outer_tab t_outer_tab_t;
begin
    inner_tab := t_inner_tab_t('one', 'two', 'three');

    outer_tab := t_outer_tab_t();

    outer_tab.extend;

    outer_tab(outer_tab.last) := outer_tab_t(1, inner_tab);

    forall i in outer_tab.first .. outer_tab.last 
      insert all
        when r = 1 then
          into a (num) values (o)
        when r  >= 1 then
          into b (val) values (c)
        select column_value c, outer_tab(i).num o, 
               row_number() over (partition by outer_tab(i).num order by outer_tab(i).num) r 
        from   table(cast(outer_tab(i).vals as t_inner_tab_t));

end;
/

SELECT * FROM a;

NUM
---
  1 

SELECT * FROM b;


VAL                          
------------------------------
one                            
two                            
three

The row_number() clause is to enable the conditional insert (when r = 1). Without this, you'll insert into a for every value present in your outer_tab.

Best Answer

Related Solutions

Initializing Associative Array in PL/SQL

FORALL on Nested Collections

Related Question