Getting the error Reference to uninitialized collection to associative arrays

Question

I am using associative arrays in a package I am creating and I get the error ORA-06531: Reference to uninitialized collection if I try to get the count (TBL.COUNT) before I have bulk collected into the array.

I have found that I can use EXISTS(1) to check if there is something there instead of the count but if I am not bulk collecting into it, how do I get the index I need to use for the next row?

loc_idx := TBL.COUNT; 
OR  
TBL(TBL.COUNT+1) := blah;

My thought was that you didn't need to initialize associative arrays unlike Nested tables and varrays

Here is an example of one I am using

TYPE invc_ln_item_type                  IS TABLE OF invc_ln_item%ROWTYPE;
invc_ln_item_tbl                        invc_ln_item_type;

used as an input to the following proc

    PROCEDURE CREATE_INVC_LN_ITEM(P_LN_ITEM_TYPE_CD IN 
                                  invc_ln_item.ln_item_type_cd%TYPE, 
                                  P_LN_ITEM_SBTYPE_CD IN 
                                  invc_ln_item.ln_item_sbtype_cd%TYPE, 
                                  P_INVC_PK IN invc.invc_pk%TYPE,
                                  P_INVC_LN_ITEM_TBL IN OUT 
                                  invc_ln_item_type)
    IS
    loc_inv_ln_item_rec INVC_LN_ITEM%ROWTYPE;
    loc_idx             NUMBER;
BEGIN

    loc_idx := P_INVC_LN_ITEM_TBL.COUNT + 1;

    INSERT INTO APP.INVC_LN_ITEM (INVC_LN_ITEM_PK, 
                                  INSRT_DT, 
                                  INSRT_USER, 
                                  LAST_UPDT_DT, 
                                  LAST_UPDT_USER, 
                                  LN_ITEM_TYPE_CD, 
                                  LN_ITEM_SBTYPE_CD, 
                                  INVC_PK, 
                                  UNITS, 
                                  AMT)
                          VALUES (null,
                                  null,
                                  null,
                                  null,
                                  null,
                                  P_LN_ITEM_TYPE_CD,
                                  P_LN_ITEM_SBTYPE_CD,
                                  P_INVC_PK,
                                  0,
                                  0)
                        RETURNING INVC_LN_ITEM_PK, 
                                  INSRT_DT, 
                                  INSRT_USER, 
                                  LAST_UPDT_DT, 
                                  LAST_UPDT_USER, 
                                  LN_ITEM_TYPE_CD, 
                                  LN_ITEM_SBTYPE_CD, 
                                  INVC_PK, 
                                  UNITS, 
                                  AMT
                             INTO loc_inv_ln_item_rec; 

    P_INVC_LN_ITEM_TBL(loc_idx) := loc_inv_ln_item_rec;

END;

Then gets called like

CREATE_INVC_LN_ITEM(P_BILLG_PRFL_LN_ITEM_REC.ln_item_type_cd, 
                                    billg_prfl_ln_item_sbtype_tbl(c).ln_item_sbtype_cd, 
                                    invc_rec.invc_pk,
                                    invc_ln_item_tbl);

The errors in the case above occurs at: loc_idx := P_INVC_LN_ITEM_TBL.COUNT + 1;

[Error] Execution (39: 1): ORA-06531: Reference to uninitialized collection

Are you sure that you are using associative array? Please share the code. Edit: why are you using (or trying to use) associative array if your collection will be densely populated? — Goran Stefanović, Oct 04 '17 at 15:01
Possible duplicate of [Reference to uninitialized collection PL/SQL](https://stackoverflow.com/questions/25201903/reference-to-uninitialized-collection-pl-sql) — XING, Oct 04 '17 at 15:17
The post listed above is for a nested table. This post is about a associative array — programmerNOOB, Oct 04 '17 at 16:22
If anyone is curioius, I solved this. When defining the TYPE, I forgot the: INDEX BY PLS_INTEGER — programmerNOOB, Oct 04 '17 at 16:27
@programmerNOOB `When defining the TYPE, I forgot the: INDEX BY PLS_INTEGER` then your collection no more existed as associative array. it got converted to nested table and that's why above link was posted for you. Also error like `Reference to uninitialized collection PL/SQL` occurs only with Nested tables coz in Associative arrays we donot need to intialize anything. So actually that was a pointer which ` Goran Stefanović` pointed very well, — XING, Oct 05 '17 at 08:16

MT0 · Answer 1 · 2017-10-04T15:10:29.060

There is not an incrementing index for an associative array. You do not need to initialise an associative array and using array.COUNT will not raise an exception with an associative array (unlike for collections).

DECLARE
  TYPE arraytype IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(2);

  array arraytype;
  idx VARCHAR2(2);
BEGIN
  DBMS_OUTPUT.PUT_LINE( array.COUNT ); -- 0
  array('A') := 'AAAA';
  DBMS_OUTPUT.PUT_LINE( array.COUNT ); -- 1
  array(array.COUNT + 1) := 'BBBB';
  DBMS_OUTPUT.PUT_LINE( array.COUNT ); -- 2
  array('7') := 'DDDD';
  DBMS_OUTPUT.PUT_LINE( array.COUNT ); -- 3

  idx := array.FIRST;
  WHILE idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE( idx || ' - ' || array(idx) );
    idx := array.NEXT( idx );
  END LOOP;
END;
/

If you are expecting to use an incrementing index then you need to consider whether an associative array is the correct type to be using and whether a collection or a VARRAY would be better.

If you are using collections (not associative arrays):

DECLARE
  TYPE arraytype IS TABLE OF VARCHAR2(20);

  array arraytype;
BEGIN
  array := arraytype(); -- Initialise
  DBMS_OUTPUT.PUT_LINE( array.COUNT ); -- 0
  array.EXTEND;                        -- Extend by 1 element
  array(1) := 'AAAA';
  DBMS_OUTPUT.PUT_LINE( array.COUNT ); -- 1
  array.EXTEND(1);                     -- Number of elements to extend by
  array(array.COUNT) := 'BBBB';
  DBMS_OUTPUT.PUT_LINE( array.COUNT ); -- 2
  array.EXTEND;
  array(3) := 'DDDD';
  DBMS_OUTPUT.PUT_LINE( array.COUNT ); -- 3

  FOR idx IN 1 .. array.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE( idx || ' - ' || array(idx) );
  END LOOP;
END;
/

This was my thoughts as well, So I am just wondering how I am getting this. I have defined the AAs at the spec level and they are passed around to procs within the package. Could that have something to do with it? I will try to add more substance to the original post — programmerNOOB, Oct 04 '17 at 15:17

Getting the error Reference to uninitialized collection to associative arrays

1 Answers1