Skip to Main Content
  • Questions
  • ORA-06530: Reference to uninitialized composite

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: May 31, 2020 - 11:07 am UTC

Answered by: Chris Saxon - Last updated: June 01, 2020 - 2:46 pm UTC

Category: PL/SQL - Version: 12.2.0.1.0

Viewed 100+ times

You Asked

getting the ORA-06530: Reference to uninitialized composite
with below code.. you will be able to simulate the issue.
thanks in advance
CREATE TYPE "NUMBER_TABLE" AS TABLE OF NUMBER
/

CREATE TYPE TYP_BOARD_PACKAGE_OBJ AS OBJECT
(
   BOARD_PACKAGE_INTERNAL_ID NUMBER (20),
   BOARD_PACKAGE_ID NUMBER (20),
   BOARD_PACKAGE_CREATE_DT DATE,
   BOARD_PACKAGE_NAME VARCHAR2 (150 CHAR),
   BOARD_PACKAGE_NEXT_REVIEW_DT DATE,
   BOARD_PACKAGE_LAST_REVIEW_DT DATE,
   OVERSIGHT_COMMITTEE_ID NUMBER (20),
   OVERSIGHT_COMMITTEE_NAME VARCHAR2 (150 CHAR)
);
/

CREATE TYPE TYP_BOARD_PERSON_OBJ AS OBJECT
(
   PERSON_ID NUMBER (20),
   DIRECTORY_FULL_NAME VARCHAR2 (100 BYTE),
   BOARD_PERSON_ROLE_CD VARCHAR2 (12 CHAR)
);
/

CREATE TYPE TYP_BOARD_PERSON_TBL IS TABLE OF TYP_BOARD_PERSON_OBJ;
/

CREATE TYPE TYP_PORTFOLIO_OBJ AS OBJECT
(
   PORTFOLIO_ID NUMBER (20),
   PORTFOLIO_SHORT_NAME VARCHAR2 (15 BYTE),
   PORTFOLIO_LEGAL_NAME VARCHAR2 (150 BYTE)
);
/

CREATE TYPE TYP_PORTFOLIO_TBL IS TABLE OF TYP_PORTFOLIO_OBJ;
/

CREATE TYPE TYP_BOARD_DASH_OBJ AS OBJECT
(
   PACKAGE_DTLS TYP_BOARD_PACKAGE_OBJ,
   ROLE_DTLS TYP_BOARD_PERSON_TBL,
   PORTF_DTLS TYP_PORTFOLIO_TBL
);
/

CREATE TYPE TYP_BOARD_DASH_TBL IS TABLE OF TYP_BOARD_DASH_OBJ;
/

INSERT INTO BOARD_PACKAGE_DETAIL
     VALUES (1001,
             1,
             TRUNC (SYSDATE),
             'One',
             NULL,
             NULL,
             'Y',
             1001,
             'FOS_TOW',
             SYSTIMESTAMP)
/

CREATE TABLE BOARD_PACKAGE_DETAIL
(
   BOARD_PACKAGE_INTERNAL_ID      NUMBER (20) CONSTRAINT BOARD_PACKAGE_DETAIL_NN1 NOT NULL,
   BOARD_PACKAGE_ID               NUMBER (20) CONSTRAINT BOARD_PACKAGE_DETAIL_NN2 NOT NULL,
   BOARD_PACKAGE_CREATE_DT        DATE        CONSTRAINT BOARD_PACKAGE_DETAIL_NN3 NOT NULL
                                     CONSTRAINT BOARD_PACKAGE_DETAIL_CC1 CHECK
                                        (BOARD_PACKAGE_CREATE_DT =
                                            TRUNC (BOARD_PACKAGE_CREATE_DT)),
   BOARD_PACKAGE_NAME             VARCHAR2 (150 CHAR),
   BOARD_PACKAGE_NEXT_REVIEW_DT   DATE
                                     CONSTRAINT BOARD_PACKAGE_DETAIL_CC2 CHECK
                                        (   BOARD_PACKAGE_NEXT_REVIEW_DT
                                               IS NULL
                                         OR (BOARD_PACKAGE_NEXT_REVIEW_DT =TRUNC (BOARD_PACKAGE_NEXT_REVIEW_DT))),
   BOARD_PACKAGE_LAST_REVIEW_DT   DATE
                                     CONSTRAINT BOARD_PACKAGE_DETAIL_CC3 CHECK
                                        (   BOARD_PACKAGE_LAST_REVIEW_DT
                                               IS NULL
                                         OR (BOARD_PACKAGE_LAST_REVIEW_DT =TRUNC (BOARD_PACKAGE_LAST_REVIEW_DT))),
   ACTIVE_IND                     VARCHAR2 (1 CHAR)
                                     CONSTRAINT BOARD_PACKAGE_DETAIL_NN4 NOT NULL
                                     CONSTRAINT BOARD_PACKAGE_DETAIL_CC4 CHECK
                                        (    ACTIVE_IND IN ('Y', 'N')
                                         AND ACTIVE_IND = UPPER (ACTIVE_IND)),
   OVERSIGHT_COMMITTEE_ID         NUMBER (20)
                                     CONSTRAINT BOARD_PACKAGE_DETAIL_NN5 NOT NULL,
   UPDATE_ID                      VARCHAR2 (30 CHAR)
                                     CONSTRAINT BOARD_PACKAGE_DETAIL_NN6 NOT NULL,
   UPDATE_TMSTMP                  TIMESTAMP (6)
                                     CONSTRAINT BOARD_PACKAGE_DETAIL_NN7 NOT NULL,
   CONSTRAINT BOARD_PACKAGE_DETAIL_UK1 PRIMARY KEY
      (BOARD_PACKAGE_INTERNAL_ID),
   CONSTRAINT PACKAGE_PORTFOLIO_REPORT_AK UNIQUE
      (BOARD_PACKAGE_ID, BOARD_PACKAGE_CREATE_DT)
)
/

CREATE PACKAGE fos_board_ops_admin_pkg
   AUTHID DEFINER
AS
   PROCEDURE get_package_details (pv_corp_id             VARCHAR2 DEFAULT NULL,
                                  pv_pkg_int_id   IN OUT NUMBER_TABLE,
                                  pv_pkg_id              NUMBER,
                                  pv_pkg_name            VARCHAR2,
                                  pv_pkg_dtls        OUT typ_board_dash_tbl,
                                  pv_sucess          OUT VARCHAR2);
END fos_board_ops_admin_pkg;
/

CREATE PACKAGE BODY fos_board_ops_admin_pkg
AS
   PROCEDURE get_package_details (pv_corp_id             VARCHAR2 DEFAULT NULL,
                                  pv_pkg_int_id   IN OUT NUMBER_TABLE,
                                  pv_pkg_id              NUMBER,
                                  pv_pkg_name            VARCHAR2,
                                  pv_pkg_dtls        OUT typ_board_dash_tbl,
                                  pv_sucess          OUT VARCHAR2)
   IS
      l_int_id       NUMBER_TABLE;
      l_pop_seq      NUMBER (10) := 0;
      l_person_seq   NUMBER (10) := 0;
      l_portf_seq    NUMBER (10) := 0;
   BEGIN
      DBMS_OUTPUT.put_line ('started');
      pv_pkg_dtls := typ_board_dash_tbl ();
      DBMS_OUTPUT.put_line (pv_pkg_int_id.COUNT);

      IF pv_pkg_int_id.COUNT = 0
      THEN
         IF pv_pkg_id IS NULL
         THEN
            SELECT BOARD_PACKAGE_INTERNAL_ID
              BULK COLLECT INTO pv_pkg_int_id
              FROM board_package_detail
             WHERE     active_ind = 'Y'
                   AND BOARD_PACKAGE_NAME LIKE '%' || pv_pkg_name || '%';
         ELSIF pv_pkg_name IS NULL
         THEN
            SELECT BOARD_PACKAGE_INTERNAL_ID
              BULK COLLECT INTO pv_pkg_int_id
              FROM board_package_detail
             WHERE     active_ind = 'Y'
                   AND BOARD_PACKAGE_ID LIKE '%' || pv_pkg_id || '%';
         END IF;
      END IF;

      DBMS_OUTPUT.put_line ('started1a');

      FOR i
         IN (SELECT *
               FROM board_package_detail
              WHERE     BOARD_PACKAGE_INTERNAL_ID IN
                           (SELECT *
                              FROM TABLE (pv_pkg_int_id))
                    AND active_ind = 'Y')
      LOOP
         DBMS_OUTPUT.put_line ('started0');
         l_person_seq := 0;
         l_portf_seq := 0;
         pv_pkg_dtls.EXTEND ();
         DBMS_OUTPUT.put_line ('last ' || pv_pkg_dtls.LAST);
         l_pop_seq := 1;
         DBMS_OUTPUT.put_line ('l_pop_seq ' || l_pop_seq);
         pv_pkg_dtls (pv_pkg_dtls.LAST).PACKAGE_DTLS :=
            TYP_BOARD_PACKAGE_OBJ (NULL,
                                   NULL,
                                   NULL,
                                   NULL,
                                   NULL,
                                   NULL,
                                   NULL,
                                   NULL);
         DBMS_OUTPUT.put_line ('l_pop_seq1 ' || l_pop_seq);
         pv_pkg_dtls (pv_pkg_dtls.LAST).PACKAGE_DTLS :=
            TYP_BOARD_PACKAGE_OBJ (i.BOARD_PACKAGE_INTERNAL_ID,
                                   i.BOARD_PACKAGE_ID,
                                   i.BOARD_PACKAGE_CREATE_DT,
                                   i.BOARD_PACKAGE_NAME,
                                   i.BOARD_PACKAGE_NEXT_REVIEW_DT,
                                   i.BOARD_PACKAGE_LAST_REVIEW_DT,
                                   i.OVERSIGHT_COMMITTEE_ID,
                                   'ABCD');
      END LOOP;
   END;
END fos_board_ops_admin_pkg;
/

DECLARE
   l_corp_id         VARCHAR2 (100) := '878978';
   l_pv_pkg_int_id   NUMBER_TABLE := NUMBER_TABLE ();
   l_pv_pkg_id       NUMBER (20);
   l_pv_pkg_name     VARCHAR2 (150);
   l_pv_pkg_dtls     typ_board_dash_tbl := typ_board_dash_tbl ();
   l_pv_sucess       VARCHAR2 (4000);
   l_cnt             NUMBER (10) := 0;
BEGIN
   l_pv_pkg_int_id.EXTEND ();
   l_pv_pkg_int_id (1) := 1001;
   DBMS_OUTPUT.put_line ('started');

   FOS_BOARD_OPS_ADMIN_PKG.get_package_details (l_corp_id,
                                                l_pv_pkg_int_id,
                                                l_pv_pkg_id,
                                                l_pv_pkg_name,
                                                l_pv_pkg_dtls,
                                                l_pv_sucess);
END;
/

and we said...

The problem is here:

         pv_pkg_dtls (pv_pkg_dtls.LAST).PACKAGE_DTLS :=
            TYP_BOARD_PACKAGE_OBJ (NULL,
                                   NULL,
                                   NULL,
                                   NULL,
                                   NULL,
                                   NULL,
                                   NULL,
                                   NULL);


PACKAGE_DTLS is an attribute of TYP_BOARD_DASH_OBJ. You need to initialize this object!

For example:

declare
  pv_pkg_dtls typ_board_dash_tbl;
begin
  pv_pkg_dtls := typ_board_dash_tbl ();
  pv_pkg_dtls.extend ();
      
  pv_pkg_dtls (1) := typ_board_dash_obj (
      typ_board_package_obj (
        null, null, null, null, null, null, null, null
      ), 
      typ_board_person_tbl(), 
      typ_portfolio_tbl() 
  );
end;
/

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.