create or replace TYPE GAP_ITEM_VALUE_REC AS OBJECT
( LOCATION         Number(10),    
  LOCATION_TYPE    CHAR(1),		
  LGCY_PRODUCT_NO  Varchar2(15), 
  LGCY_SIZE_CD     Varchar2(4),	
  RMS_ITEM_NO	   Varchar2(25),  
  RMS_PACK_IND	   Char(1),	      
  CORP_ID          Varchar2(10), 
  MDSE_CO_ID	   Varchar2(10),  
  STATUS		   Char(1),       
  ERROR_CODE       Number(5),    
  ERROR_DESC	   Varchar2(255));

create or replace TYPE GAP_ITEM_VALUE_TBL IS TABLE OF GAP_ITEM_VALUE_REC;

/


DECLARE
L_VALUE_tbl              GAP_ITEM_VALUE_TBL;
   --
   CURSOR C_VALUE_RECS IS
       SELECT DISTINCT from_loc location,
                            'W' location_type,
                            product_no lgcy_product_no,
                            size_code lgcy_size_cd,
                            NULL rms_item_no,
                            NULL rms_pack_ind,
                            corp_id corp_id,
                            company_id mdse_co_id,
                            NULL status,
                            NULL error_code,
                            NULL error_desc
             FROM ris_alloc_stage_gtt;
BEGIN
   OPEN C_VALUE_RECS;
   FETCH C_VALUE_RECS BULK COLLECT INTO L_VALUE_tbl;
   CLOSE C_VALUE_RECS;

END;

SQL> CREATE OR REPLACE TYPE dept_type AS OBJECT (
  2    deptno NUMBER(2),
  3    dname  VARCHAR2(14),
  4    loc    VARCHAR2(13)
  5  )
  6  /

Type created.

SQL> CREATE OR REPLACE TYPE dept_tab_type IS TABLE OF dept_type
  2  /

Type created.

SQL> DECLARE
  2    l_value_tbl dept_tab_type;
  3    CURSOR c_value_recs IS SELECT deptno, dname, loc FROM dept;
  4  BEGIN
  5    OPEN c_value_recs;
  6    FETCH c_value_recs BULK COLLECT INTO l_value_tbl;
  7    CLOSE c_value_recs;
  8  END;
  9  /
  FETCH c_value_recs BULK COLLECT INTO l_value_tbl;
                                       *
ERROR at line 6:
ORA-06550: line 6, column 40:
PLS-00386: type mismatch found at 'L_VALUE_TBL' between FETCH cursor and INTO variables
ORA-06550: line 6, column 3:
PL/SQL: SQL Statement ignored


SQL> DECLARE
  2    l_value_tbl dept_tab_type;
  3    CURSOR c_value_recs IS SELECT dept_type(deptno, dname, loc) FROM dept;
  4  BEGIN
  5    OPEN c_value_recs;
  6    FETCH c_value_recs BULK COLLECT INTO l_value_tbl;
  7    CLOSE c_value_recs;
  8  END;
  9  /

PL/SQL procedure successfully completed.