i have formatted the code.. please check 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; /