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;

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

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 :=

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