Creating nested associative arrays in Oracle 11g

Question

I have defined associative array in Oracle as below

CREATE OR REPLACE TYPE outlier AS OBJECT (
 ACCOUNT_ID  varchar2(100),
   INC_MONTH date,
    ); 

create or replace type outlier_acc_tab as table of outlier ;

CREATE OR REPLACE TYPE outlier_detail_rec AS OBJECT (

 NAME varchar2(100),
 OUTLIER_ACCOUNT_LIST outlier_acc_tab

);

create or replace type outlier_detail_tab as table of outlier_detail_rec;

Currently I am getting names repeated in all the OUTLIER_ACCOUNT_LIST array. Instead I want to group multiple account details for a single name. Please let me know how the definition of arrays should be modified. Thanks for your help.

I am looking for an output like this

"OUTLIER_LIST":    [
        {
     "NAME": "A",
     "OUTLIER_ACCOUNT_LIST":          [{
        "ACCOUNT_ID": "361",
        "INC_MONTH ": 1475208000000

     }
  }, 
             {
        "ACCOUNT_ID": "362",
        "INC_MONTH": 1475208000000,

     }
  }
             ]
]
}

My select statement in the package is

  SELECT outlier_detail_rec (
          NAME   => NAME,
     outlier_acc_tab (
            outlier_acc_rec(
               ACCOUNT_ID                   => ACCOUNT_ID,
               INC_MONTH         =>  INC_MONTH

) ))
    BULK COLLECT INTO v_outlier_array
    FROM outlier_Sel;

score 1 · Answer 1 · answered Nov 29 '16 at 22:38

What you have are not oracle Associative Arrays, they are oracle Nested Tables. However, bulk collection does not work with Associative Arrays, but it does with Nested Tables.

An associative array of outlier_account_lists would be:

TYPE outlier_detail_aa AS TABLE OF outlier_acc_tab INDEX BY VARCHAR2(100);

To load such an AA you'll need to do something like this:

declare
  type outlier_detail_aa as table of outlier_acc_tab index by varchar2(100)
  outlier_detail outlier_detail_aa;
begin
  for r in (select distinct name from outlier_sel)
  loop
    select outliser_acc_rec(account_id, inc_month)
      bulk collect into outlier_detail(r.name)
      from outlier_sel
     where name = r.name;
  end loop;
end;

32978bet · Answer 2 · 2018-01-03T07:39:56.880

The statement "bulk collection does not work with Associative Arrays" is wrong. Bulk collect into also works with associative arrays.

type units_rec is record(...)
type unit_tab is table of units_rec index by binary_integer; -- <--associative array
volume_units unit_tab;

select distinct some columns
bulk collect into wul_units
from <table>;

but it will not work for a nested assiociative array. In your case you will need to fill the arrays "manually" with a cursor loop. I never used object (only record) so I don't know the exact syntax. But it should look like

-> create a associative array index by binary integer, where the index will be the account id

-> create a record for outlier_list, where one column is of type of the array created above, and then an associative array from this record (or try it with objects)

i number:=0;

for crs in (SELECT <columns> FROM outlier_Sel) loop
  outlier_list(i).name := crs.outlier_list_name;
  outlier_list(i)(crs.account_id) := crs.account_name;
  ...
end loop;

Creating nested associative arrays in Oracle 11g

2 Answers2