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;
/
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;
/