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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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.