3 Replies Latest reply: Feb 22, 2013 5:53 AM by Nitesh. RSS

    Reference to uninitialized collection - Please Help

    Anu
      Hi All,

      Please help me on below error

      I Created two Objects and two Type like below

      Create the Object*

      CREATE OR REPLACE TYPE P_LVL1_A1_OBJ AS OBJECT
      (A1 VARCHAR2(250),
      A2 VARCHAR2(500),
      A3 VARCHAR2(100));

      Create Table Type*

      CREATE OR REPLACE TYPE P_LVL1_A1 AS TABLE OF P_LVL1_A1_OBJ;

      Create the Object

      CREATE OR REPLACE TYPE P_LVL2_B1_OBJ AS OBJECT
      (B1 VARCHAR2(250),
      B2 VARCHAR2(500),
      B3 VARCHAR2(250));

      Create Table Type*

      CREATE OR REPLACE TYPE P_LVL2_B1 AS TABLE OF P_LVL2_B2_OBJ;

      Now i am creating one package like

      My Package_

      CREATE OR REPLACE PACKAGE PKG_CHECK_1 AS
      PROCEDURE PRC_CHECK_1(X1 IN P_LVL1_A1,
      Y1 IN P_LVL2_B1);
      END PKG_CHECK_1;


      create or replace
      PACKAGE BODY PKG_CHECK_1 AS

      PROCEDURE PRC_CHECK_1(X1 IN P_LVL1_A1,
      Y1 IN P_LVL2_B1) AS


      v_chk_rec NUMBER;
      v_nm_id NUMBER;
      v_abc_nm_id NUMBER;

      FOR I IN 1..X1.COUNT
      LOOP
      SELECT COUNT(*) INTO v_chk_rec FROM XYZ WHERE NM_1 = X1(I).A1;
      IF v_chk_rec = 0 THEN
      SELECT NVL(MAX(NM_ID),0) + 1 INTO v_nm_id XYZ;
      INSERT INTO XYZ
      VALUES(v_nm_id,
      NVL(X1(I).A1,'-1'),
      NVL(X1(I).A2,'-1'),
      NVL(X1(I).A3,'-1'));
      COMMIT;
      END IF;
      END LOOP;

      FOR I IN 1..Y1.COUNT
      LOOP
      SELECT COUNT(*) INTO v_chk_rec FROM ABC WHERE NM_1 = Y1(I).A1;
      IF v_chk_rec = 0 THEN
      SELECT NVL(MAX(NM_ID),0) + 1 INTO v_abc_nm_id XYZ;
      INSERT INTO XYZ
      VALUES(v_abc_nm_id,
      NVL(Y1(I).B1,'-1'),
      NVL(Y1(I).B2,'-1'),
      NVL(Y1(I).B3,'-1'));
      COMMIT;
      END IF;
      END LOOP;
      END PRC_CHECK_1;
      END PKG_CHECK_1;


      When i am calling this Package i am passing value for Y1 and i am not sending any data for X1 then i me getting the below error.

      ORA-06531
      Reference to uninitialized collection

      How I handle this within the Package.

      Please help me on this Issue..

      Thanks In Advance.
      Anu.
        • 1. Re: Reference to uninitialized collection - Please Help
          jeneesh
          Anu wrote:

          My Package_
          Are you saying your package compiled without any error?
          CREATE OR REPLACE
          PACKAGE BODY PKG_CHECK_1
          AS
          PROCEDURE PRC_CHECK_1(
              X1 IN P_LVL1_A1,
              Y1 IN P_LVL2_B1)
          AS
            v_chk_rec   NUMBER;
            v_nm_id     NUMBER;
            v_abc_nm_id NUMBER;
                                                 --"No Begin"
            FOR I IN 1..X1.COUNT
            LOOP
              SELECT COUNT(*) INTO v_chk_rec FROM XYZ WHERE NM_1 = X1(I).A1;
              IF v_chk_rec = 0 THEN
                SELECT NVL(MAX(NM_ID),0) + 1 INTO v_nm_id XYZ; --"no FROM"
                INSERT
                INTO XYZ VALUES
                  (
                    v_nm_id,
                    NVL(X1(I).A1,'-1'),
                    NVL(X1(I).A2,'-1'),
                    NVL(X1(I).A3,'-1')
                  );
                COMMIT;
              END IF;
            END LOOP;
            FOR I IN 1..Y1.COUNT
            LOOP
              SELECT COUNT(*) INTO v_chk_rec FROM ABC WHERE NM_1 = Y1(I).A1;
              IF v_chk_rec = 0 THEN
                SELECT NVL(MAX(NM_ID),0) + 1 INTO v_abc_nm_id XYZ;
                INSERT
                INTO XYZ VALUES
                  (
                    v_abc_nm_id,
                    NVL(Y1(I).B1,'-1'),
                    NVL(Y1(I).B2,'-1'),
                    NVL(Y1(I).B3,'-1')
                  );
                COMMIT;
              END IF;
            END LOOP;
          END PRC_CHECK_1;
          END PKG_CHECK_1;
          • 2. Re: Reference to uninitialized collection - Please Help
            Nitesh.
            There are many basic kind of errors in your package that can be rectified easily but solution generally to your problem is initialisation error coming means its you have to initialize your type objects as in this way


            DECLARE
            a11 p_lvl1_a1 := NEW p_lvl1_a1 ();
            a1_obj p_lvl1_a1_obj := NEW p_lvl1_a1_obj (NULL, NULL, NULL); -- initilisatiion
            b11 p_lvl2_b1 := NEW p_lvl2_b1 ();
            b1_ob p_lvl2_b1_obj := NEW p_lvl2_b1_obj (NULL, NULL, NULL);
            BEGIN
            a11.EXTEND;
            a1_obj.a1 := NULL; -- give some value here
            a1_obj.a2 := NULL;
            a11 (1) := a1_obj;
            same for all type objects as sending values to parameters so try same in package while calling
            END;

            Edited by: Niteshkhush on Feb 22, 2013 5:15 PM

            Edited by: Niteshkhush on Feb 22, 2013 5:17 PM
            • 3. Re: Reference to uninitialized collection - Please Help
              Nitesh.
              DECLARE
              a11 p_lvl1_a1 := NEW p_lvl1_a1 ();
              a1_obj p_lvl1_a1_obj := NEW p_lvl1_a1_obj (NULL, NULL, NULL);
              b11 p_lvl2_b1 := NEW p_lvl2_b1 ();
              b1_obj p_lvl2_b1_obj := NEW p_lvl2_b1_obj (NULL, NULL, NULL);
              BEGIN
              a11.EXTEND;
              a1_obj.a1 := NULL; --- Give some values as per your requirement for variables ..
              a1_obj.a2 := NULL;
              a1_obj.a3 := NULL;
              a11 (1) := a1_obj;
              b11.EXTEND;
              b1_obj.b1 := NULL;
              b1_obj.b2 := NULL;
              b1_obj.b3 := NULL;
              b11 (1) := b1_obj;
              psdba.pkg_check_1.prc_check_1 (x1 => a11, y1 => b11);
              END;

              You check your package's small errors and then test in this above mentioned it will work out ..