Home » SQL & PL/SQL » SQL & PL/SQL » BULK COLLECT ISSUE (Oracle 12C, Unix)
BULK COLLECT ISSUE [message #668253] Wed, 14 February 2018 17:23 Go to next message
bahubcd
Messages: 39
Registered: July 2007
Location: Bangalore
Member
Hi all,
Can someone tell me why the below PLSQL block does not work and how to make it work?

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;



Error that I get is
PLS-00386: type mismatch found at 'L_VALUE_TBL' between FETCH cursor and INTO variables.

I would like to do a bulk collect. Is it an issue with the L_value_tbl being an object type and not a table type?

Regards,
Bahubcd
Re: BULK COLLECT ISSUE [message #668254 is a reply to message #668253] Wed, 14 February 2018 18:02 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
Why are you attempting to load the whole table ris_alloc_stage_gtt into PGA?
Re: BULK COLLECT ISSUE [message #668256 is a reply to message #668254] Wed, 14 February 2018 23:32 Go to previous messageGo to next message
bahubcd
Messages: 39
Registered: July 2007
Location: Bangalore
Member
Thanks for getting back Blackswan.

It is a gtt, where I am saving a subset of records from the main table for processing.

Any help to fix this would be greatly appreciated!
Re: BULK COLLECT ISSUE [message #668257 is a reply to message #668256] Wed, 14 February 2018 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
bahubcd wrote on Wed, 14 February 2018 21:32
Thanks for getting back Blackswan.

It is a gtt, where I am saving a subset of records from the main table for processing.

Any help to fix this would be greatly appreciated!

How can you save subset when no WHERE clause exists?

Fix what?
You post no problem.
Re: BULK COLLECT ISSUE [message #668258 is a reply to message #668253] Thu, 15 February 2018 00:09 Go to previous message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The problem is NOT in BULK COLLECT it is in your variable or query.
You return 11 columns and give only ONE variable.

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.

[Updated on: Thu, 15 February 2018 00:10]

Report message to a moderator

Previous Topic: How to get the functions name used by a column in the whole database
Next Topic: Need help with insert in Oracle
Goto Forum:
  


Current Time: Sat Nov 17 00:28:02 CST 2018