6 Replies Latest reply: Mar 31, 2013 10:14 AM by gaverill RSS

    ORA-06531: Reference to uninitialized collection

    621483
      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_
          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
            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
              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
                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
                  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
                    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