This discussion is archived
6 Replies Latest reply: Mar 31, 2013 8:14 AM by gaverill RSS

ORA-06531: Reference to uninitialized collection

621483 Explorer
Currently Being Moderated
Hi,

While migrating from Oracle 9i to Oracle 10g we are getting ORA-06531: Reference to uninitialized collection error.
We were able to resolve this by intializing the arrays where ever they were declared.However, i want to know that
if there is some generalized way to do it.May be a patch from Oracle.Because changing code every where is a very time consuming task.

Thx
  • 1. Re: ORA-06531: Reference to uninitialized collection
    Karthick_Arp Guru
    Currently Being Moderated
    Phantasm wrote:
    Hi,

    While migrating from Oracle 9i to Oracle 10g we are getting ORA-06531: Reference to uninitialized collection error.
    We were able to resolve this by intializing the arrays where ever they were declared.However, i want to know that
    if there is some generalized way to do it.May be a patch from Oracle.Because changing code every where is a very time consuming task.

    Thx
    10g is doing it in the correct way. Before that it has been done in the wrong way. So yes i think you have no option other than modifying your code.

    check this link.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5675651707870#13115487212260
  • 2. Re: ORA-06531: Reference to uninitialized collection
    Avinash Tripathi Pro
    Currently Being Moderated
    Hi,
    It seems in 10G collections and subcollections are not initialized by default. See the metalink notes 333955.1 for more details.

    -Avinash
  • 3. Re: ORA-06531: Reference to uninitialized collection
    621483 Explorer
    Currently Being Moderated
    Hi,

    I have already progressed with the code changes and have completed some of them successfully.However, i'm stuck
    at pieces of code where there are in and out parameters in functions and procedures, which have the datatype refrencing to a collection.How to initilaize the collections in such type of cases.

    For eg:

    TYPE a_rec IS RECORD (
    a VARCHAR2(100),
    b VARCHAR2(100)
    );

    TYPE a_array IS TABLE OF a_rec;

    TYPE a_array_rec IS RECORD (
    c VARCHAR2(255),
    d VARCHAR2(80),
    e a_array
    );

    TYPE a_array_array IS TABLE OF a_array_rec;
    Function x (y in out a_array_array)
    Is
    Begin
    ....
    END;

    Otherwise if you have some example pls suggest.

    Thx
  • 4. Re: ORA-06531: Reference to uninitialized collection
    Hoek Guru
    Currently Being Moderated
    See:
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:208012348074
  • 5. Re: ORA-06531: Reference to uninitialized collection
    912619 Newbie
    Currently Being Moderated
    DECLARE
    TYPE T1 IS RECORD
    (
    p_branch VARCHAR2(20),
    p_account VARCHAR2(30)
    );
    TYPE t2 IS VARRAY(20) OF t1;
    t3 t2;
    t NUMBER := 1;
    BEGIN
    FOR j IN (SELECT cust_ac_no,branch_code FROM STTM_CUST_ACCOUNT WHERE ROWNUM <= 10)
    LOOP

    DBMS_OUTPUT.PUT_LINE('YUDHI # '||J.CUST_AC_NO);
    t3(t).p_branch := j.branch_code;
    END LOOP;

    END;

    this code is not working . if any thing messed out please let me know.
  • 6. Re: ORA-06531: Reference to uninitialized collection
    gaverill Journeyer
    Currently Being Moderated
    You need to both initialize and extend the array before adding elements:
    DECLARE
         TYPE T1 IS RECORD
         (
              p_branch VARCHAR2(20),
              p_account VARCHAR2(30)
         );
    
         TYPE t2 IS VARRAY(20) OF t1;
    
         t3 t2    := t2();          <--- initialize to non-null, empty array
         t NUMBER := 1;
    BEGIN
         FOR j IN (SELECT cust_ac_no,branch_code FROM STTM_CUST_ACCOUNT WHERE ROWNUM <= 10)
         LOOP
              DBMS_OUTPUT.PUT_LINE('YUDHI # '||J.CUST_AC_NO);
    
              t3.extend();          <--- add element to end of array
    
              t3(t).p_branch := j.branch_code;
    
              t := t + 1;          <--- presumably you meant to increment your counter?
         END LOOP;
    
    END;
    Alternatively, if you don't need the counter variable t for anything else you can rid of it:
    DECLARE
         TYPE T1 IS RECORD
         (
              p_branch VARCHAR2(20),
              p_account VARCHAR2(30)
         );
    
         TYPE t2 IS VARRAY(20) OF t1;
    
         t3 t2    := t2();          <--- initialize to non-null, empty array
    BEGIN
         FOR j IN (SELECT cust_ac_no,branch_code FROM STTM_CUST_ACCOUNT WHERE ROWNUM <= 10)
         LOOP
              DBMS_OUTPUT.PUT_LINE('YUDHI # '||J.CUST_AC_NO);
    
              t3.extend();          <--- add element to end of array
    
              t3(t3.last).p_branch := j.branch_code;
         END LOOP;
    
    END;
    Gerard

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points