Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06530: Reference to uninitialized composite
ORA-06530: Reference to uninitialized composite [message #680660] Sat, 30 May 2020 10:27 Go to next message
sudheer45v
Messages: 7
Registered: May 2020
Junior Member
CREATE or REPLACE TYPE FOS_TOW_APP.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 or REPLACE TYPE FOS_TOW_APP.TYP_BOARD_PERSON_OBJ AS OBJECT
                        (
                            PERSON_ID NUMBER(20),
                            DIRECTORY_FULL_NAME     VARCHAR2 (100 Byte),
                            BOARD_PERSON_ROLE_CD VARCHAR2(12 CHAR)
                        );
/
CREATE or replace  TYPE FOS_TOW_APP.TYP_BOARD_PERSON_TBL IS TABLE OF TYP_BOARD_PERSON_OBJ;
/
CREATE or REPLACE TYPE FOS_TOW_APP.TYP_PORTFOLIO_OBJ AS OBJECT
                        (
                            PORTFOLIO_ID NUMBER(20),
                            PORTFOLIO_SHORT_NAME VARCHAR2 (15 Byte),
                            PORTFOLIO_LEGAL_NAME     VARCHAR2 (150 Byte)
                        );  
/
CREATE or replace  TYPE FOS_TOW_APP.TYP_PORTFOLIO_TBL IS TABLE OF TYP_PORTFOLIO_OBJ;
/


CREATE OR REPLACE TYPE FOS_TOW_APP.TYP_BOARD_DASH_OBJ  AS OBJECT
                       (PACKAGE_DTLS TYP_BOARD_PACKAGE_OBJ,
                        ROLE_DTLS    TYP_BOARD_PERSON_TBL,
                        PORTF_DTLS   TYP_PORTFOLIO_TBL
                        );
/
                        
CREATE or replace  TYPE FOS_TOW_APP.TYP_BOARD_DASH_TBL IS TABLE OF TYP_BOARD_DASH_OBJ;
/

 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
      gv_begin_time := SYSTIMESTAMP;
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 FOS_TOW.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 FOS_TOW.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 fos_tow.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('started4');
         dbms_output.put_line('started5');
         dbms_output.put_line('Count' ||pv_pkg_dtls.count);
         dbms_output.put_line('last '||pv_pkg_dtls.last);
         l_pop_seq :=1;--pv_pkg_dtls.count+1;
         dbms_output.put_line('l_pop_seq '||l_pop_seq);
         pv_pkg_dtls(1).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(1).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.OVE RSIGHT_COMMITTEE_ID,'DFSDF');

         
                    FOR j IN (SELECT bppr.PERSON_ID, pers.DIRECTORY_FULL_NAME, bppr.BOARD_PERSON_ROLE_CD
                      FROM FOS_TOW.BOARD_PACKAGE_PERSON_DETAIL bppd,
                           FOS_TOW.BOARD_PACKAGE_PERSON_ROLE bppr,
                           mdm.person pers
                     WHERE     bppd.BOARD_PACKAGE_INTERNAL_ID = i.BOARD_PACKAGE_INTERNAL_ID
                           AND bppd.PERSON_ROLE_ID = bppr.PERSON_ROLE_ID
                           AND bppr.PERSON_ID = pers.person_id)
                     LOOP
                             dbms_output.put_line('started2');
                             pv_pkg_dtls(l_pop_seq).ROLE_DTLS.extend();
                             l_person_seq := pv_pkg_dtls(l_pop_seq).ROLE_DTLS.count()+1;
                             pv_pkg_dtls(l_pop_seq).ROLE_DTLS(l_person_seq).PERSON_ID  :=j.PERSON_ID;
                             pv_pkg_dtls(l_pop_seq).ROLE_DTLS(l_person_seq).DIRECTORY_FULL_NAME :=j.DIRECTORY_FULL_NAME;
                             pv_pkg_dtls(l_pop_seq).ROLE_DTLS(l_person_seq).BOARD_PERSON_ROLE_CD :=j.BOARD_PERSON_ROLE_CD;
                     END LOOP;
                     
                     for k in (select port.PORTFOLIO_ID,port.PORTFOLIO_SHORT_NAME,port.PORTFOLIO_LEGAL_NAME
                                from FOS_TOW.BOARD_PACKAGE_PORTF_DETAIL BPPD,FOS_TOW.portfolio port
                                where BOARD_PACKAGE_INTERNAL_ID =1
                                and bppd.PORTFOLIO_ID =  port.PORTFOLIO_ID)
                     LOOP
                             dbms_output.put_line('started3');
                             pv_pkg_dtls(l_pop_seq).PORTF_DTLS.extend();
                             l_portf_seq := pv_pkg_dtls(l_pop_seq).PORTF_DTLS.count()+1;
                             pv_pkg_dtls(l_pop_seq).PORTF_DTLS(l_portf_seq).PORTFOLIO_ID  :=k.PORTFOLIO_ID;
                             pv_pkg_dtls(l_pop_seq).PORTF_DTLS(l_portf_seq).PORTFOLIO_SHORT_NAME :=k.PORTFOLIO_SHORT_NAME;
                             pv_pkg_dtls(l_pop_seq).PORTF_DTLS(l_portf_seq).PORTFOLIO_LEGAL_NAME :=k.PORTFOLIO_LEGAL_NAME;
                     END loop;
         
               
         end loop;
         
   END;
when i am executing with dynamic block.. i am getting the below error.

ORA-06530: Reference to uninitialized composite
ORA-06512: at "FOS_TOW_APP.FOS_BOARD_OPS_ADMIN_PKG", line 59
ORA-06512: at "FOS_TOW_APP.FOS_BOARD_OPS_ADMIN_PKG", line 59
ORA-06512: at line 14

error coming at this line
pv_pkg_dtls(1).PACKAGE_DTLS :=TYP_BOARD_PACKAGE_OBJ(null,null,null,null,null,null,null,null);


[Edit MC: add code tags]

[Updated on: Sat, 30 May 2020 11:26] by Moderator

Report message to a moderator

Re: ORA-06530: Reference to uninitialized composite [message #680661 is a reply to message #680660] Sat, 30 May 2020 10:50 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

>pv_pkg_dtls(1).PACKAGE_DTLS :=TYP_BOARD_PACKAGE_OBJ(null,null,null,null,null,null,null,null);
by my count call above includes 8 arguments by package definition appears to only allow 6 arguments. Did I miscount anything?

Re: ORA-06530: Reference to uninitialized composite [message #680662 is a reply to message #680661] Sat, 30 May 2020 11:17 Go to previous messageGo to next message
sudheer45v
Messages: 7
Registered: May 2020
Junior Member
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)

my procedure signature was 6 parameters. but 4th parameter is complex collection. below is my 4th parameter declaration and trying to assign the values.

CREATE OR REPLACE TYPE FOS_TOW_APP.TYP_BOARD_DASH_OBJ AS OBJECT
(PACKAGE_DTLS TYP_BOARD_PACKAGE_OBJ,
ROLE_DTLS TYP_BOARD_PERSON_TBL,
PORTF_DTLS TYP_PORTFOLIO_TBL
);



CREATE or REPLACE TYPE FOS_TOW_APP.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)
);


pv_pkg_dtls(1).PACKAGE_DTLS :=TYP_BOARD_PACKAGE_OBJ(null,null,null,null,null,null,null,null);
Re: ORA-06530: Reference to uninitialized composite [message #680663 is a reply to message #680660] Sat, 30 May 2020 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, make sure that lines of code do not exceed 100 characters.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects so that we will be able work to reproduce what you have.
Remove all schema and tablespace names unless they are part of the question.

ORA-06530: Reference to uninitialized composite
 *Cause:  An object, LOB, or other composite was referenced as a
          left hand side without having been initialized.
 *Action: Initialize the composite with an appropriate constructor
          or whole-object assignment.

Re: ORA-06530: Reference to uninitialized composite [message #680665 is a reply to message #680663] Sun, 31 May 2020 00:47 Go to previous messageGo to next message
sudheer45v
Messages: 7
Registered: May 2020
Junior Member
i am getting the error ORA-06530: Reference to uninitialized composite. ORacle version no :12.2.0.1.0

with below scripts you are able to reproduce the error. kindly help....
thanks in advance.

CREATE or REPLACE 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 or REPLACE 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 or replace TYPE TYP_BOARD_PERSON_TBL IS TABLE OF TYP_BOARD_PERSON_OBJ;
/
CREATE or REPLACE TYPE TYP_PORTFOLIO_OBJ AS OBJECT
(
PORTFOLIO_ID NUMBER(20),
PORTFOLIO_SHORT_NAME VARCHAR2 (15 Byte),
PORTFOLIO_LEGAL_NAME VARCHAR2 (150 Byte)
);
/
CREATE or replace TYPE TYP_PORTFOLIO_TBL IS TABLE OF TYP_PORTFOLIO_OBJ;
/
CREATE OR REPLACE 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 or replace 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 OR REPLACE 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 OR REPLACE 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;
/
Re: ORA-06530: Reference to uninitialized composite [message #680667 is a reply to message #680665] Sun, 31 May 2020 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE OR REPLACE PACKAGE fos_board_ops_admin_pkg
  2  AUTHID DEFINER
  3  AS
  4  PROCEDURE get_package_details (pv_corp_id VARCHAR2 DEFAULT NULL,
  5  pv_pkg_int_id IN OUT NUMBER_TABLE,
  6  pv_pkg_id NUMBER,
  7  pv_pkg_name VARCHAR2,
  8  pv_pkg_dtls OUT typ_board_dash_tbl,
  9  pv_sucess OUT VARCHAR2);
 10
 11  END fos_board_ops_admin_pkg;
 12  /

Warning: Package created with compilation errors.

SQL> sho err
Errors for PACKAGE FOS_BOARD_OPS_ADMIN_PKG:
LINE/COL ERROR
-------- -------------------------------------------------------------------------------------
4/1      PL/SQL: Declaration ignored
5/22     PLS-00201: identifier 'NUMBER_TABLE' must be declared
BlackSwan wrote on Sat, 30 May 2020 17:50

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
...
Michel Cadot wrote on Sat, 30 May 2020 18:24

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, make sure that lines of code do not exceed 100 characters.
...
Re: ORA-06530: Reference to uninitialized composite [message #680668 is a reply to message #680667] Sun, 31 May 2020 01:40 Go to previous messageGo to next message
sudheer45v
Messages: 7
Registered: May 2020
Junior Member
i am getting the error ORA-06530: Reference to uninitialized composite. ORacle version no :12.2.0.1.0

with below scripts you are able to reproduce the error. kindly help....
thanks in advance.

CREATE OR REPLACE TYPE "NUMBER_TABLE" AS TABLE OF NUMBER
/

CREATE or REPLACE 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 or REPLACE 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 or replace TYPE TYP_BOARD_PERSON_TBL IS TABLE OF TYP_BOARD_PERSON_OBJ;
/
CREATE or REPLACE TYPE TYP_PORTFOLIO_OBJ AS OBJECT
(
PORTFOLIO_ID NUMBER(20),
PORTFOLIO_SHORT_NAME VARCHAR2 (15 Byte),
PORTFOLIO_LEGAL_NAME VARCHAR2 (150 Byte)
);
/
CREATE or replace TYPE TYP_PORTFOLIO_TBL IS TABLE OF TYP_PORTFOLIO_OBJ;
/
CREATE OR REPLACE 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 or replace 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 OR REPLACE 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 OR REPLACE 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;
/
Re: ORA-06530: Reference to uninitialized composite [message #680669 is a reply to message #680668] Sun, 31 May 2020 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> declare
  2  l_corp_id varchar2(100):='878978';
  3  l_pv_pkg_int_id NUMBER_TABLE := NUMBER_TABLE();
  4  l_pv_pkg_id number(20);
  5  l_pv_pkg_name varchar2(150);
  6  l_pv_pkg_dtls typ_board_dash_tbl :=typ_board_dash_tbl();
  7  l_pv_sucess varchar2(4000);
  8  l_cnt number(10) :=0;
  9  begin
 10  l_pv_pkg_int_id.extend();
 11  l_pv_pkg_int_id(1) :=1001;
 12  dbms_output.put_line('started');
 13
 14  FOS_BOARD_OPS_ADMIN_PKG.get_package_details (l_corp_id,
 15  l_pv_pkg_int_id,
 16  l_pv_pkg_id,
 17  l_pv_pkg_name,
 18  l_pv_pkg_dtls,
 19  l_pv_sucess);
 20
 21  end;
 22  /
declare
*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at "MICHEL.FOS_BOARD_OPS_ADMIN_PKG", line 55
ORA-06512: at line 14
 55  pv_pkg_dtls (pv_pkg_dtls.LAST).PACKAGE_DTLS :=
pv_pkg_dtls.LAST is null as there is nothing in this pv_pkg_dtls.

For the last time:

Michel Cadot wrote on Sun, 31 May 2020 08:11
...
BlackSwan wrote on Sat, 30 May 2020 17:50

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
...
Michel Cadot wrote on Sat, 30 May 2020 18:24

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, make sure that lines of code do not exceed 100 characters.
...
FORMAT YOUR POST!
Re: ORA-06530: Reference to uninitialized composite [message #680670 is a reply to message #680669] Sun, 31 May 2020 02:54 Go to previous messageGo to next message
sudheer45v
Messages: 7
Registered: May 2020
Junior Member
i have verified pv_pkg_dtls.LAST is returning 1. as before this statement i have given the extend statement so pv_pkg_dtls.LAST will return '1' .kindly help me t0 resolve
Re: ORA-06530: Reference to uninitialized composite [message #680671 is a reply to message #680670] Sun, 31 May 2020 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your code is unreadable like this, format it first as requested.

Re: ORA-06530: Reference to uninitialized composite [message #680672 is a reply to message #680671] Sun, 31 May 2020 03:06 Go to previous messageGo to next message
sudheer45v
Messages: 7
Registered: May 2020
Junior Member
I have formatted the code.. while i pasting it was not coming properly.
anyhow attached the formatted code.
  • Attachment: Format.txt
    (Size: 8.06KB, Downloaded 91 times)
Re: ORA-06530: Reference to uninitialized composite [message #680673 is a reply to message #680672] Sun, 31 May 2020 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You didn't read the links we pasted, 6 times!

Re: ORA-06530: Reference to uninitialized composite [message #680674 is a reply to message #680673] Sun, 31 May 2020 05:57 Go to previous message
sudheer45v
Messages: 7
Registered: May 2020
Junior Member
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;
/
Previous Topic: Username length limitation in Oracle
Next Topic: count per group
Goto Forum:
  


Current Time: Sat Sep 26 01:41:56 CDT 2020