Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00497: cannot mix between single row and multi-row (BULK) in (oracle 10.2.0.4.0 - 64bi linux)
PLS-00497: cannot mix between single row and multi-row (BULK) in [message #546703] Thu, 08 March 2012 05:14 Go to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,

Please resolve this problem

 CREATE OR REPLACE PACKAGE  pkg_mkt_hub_load_collection
AS

 PROCEDURE sp_final_load_mkt_hub;

END pkg_mkt_hub_load_collection;
/


CREATE OR REPLACE PACKAGE BODY pkg_mkt_hub_load_collection
AS

 c_default_limit CONSTANT PLS_INTEGER:=5000;

 PROCEDURE sp_final_lvl_idx_mon_hub;

 PROCEDURE sp_final_lvl_idx_mon_hub
   AS
   CURSOR cur_lvl_idx_mon IS
   SELECT idxmap.ssia_index_code,idxstg.start_date,idxstg.currency,idxstg.level1,idxstg.type, 
       idxstg.return_month,idxstg.return_3months, idxstg.return_6months, idxstg.return_ytd, idxstg.return_1year, 
       idxstg.return_3years, idxstg.return_5years,idxstg.return_10years,idxstg.market_cap,
       idxstg.mkt_file_id	   
	FROM   mkt_total_lvl_indx_mon_stg idxstg, 
		   md_vendor_index_map idxmap 
	WHERE  idxmap.source = idxstg.source 
		   AND idxmap.base_currency = idxstg.currency 
		   AND idxmap.return_type = idxstg.type 
		   AND idxmap.mkt_index_id = idxstg.vendor_code
		   AND idxmap.monthly = 'Y' 
		   AND idxmap.file_type = 'T';
		   
    cur_lvl_idx_rec cur_lvl_idx_mon%ROWTYPE;
   
   BEGIN
  
   OPEN cur_lvl_idx_mon;
   LOOP
   FETCH cur_lvl_idx_mon BULK COLLECT INTO cur_lvl_idx_rec LIMIT c_default_limit;
      
   EXIT WHEN cur_lvl_idx_mon%NOTFOUND;
   
   END LOOP;
   
   CLOSE cur_lvl_idx_mon;

   
  END sp_final_lvl_idx_mon_hub;
  
  PROCEDURE sp_final_load_mkt_hub
  AS
  BEGIN
  NULL;
  END sp_final_load_mkt_hub;
   
END pkg_mkt_hub_load_collection;   
/

show error



error :
30/44    PLS-00497: cannot mix between single row and multi-row (BULK) in
         INTO list


when i removed bulk collect from fetch commands, it works fine.

please give an idea about it
Re: PLS-00497: cannot mix between single row and multi-row (BULK) in [message #546704 is a reply to message #546703] Thu, 08 March 2012 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BULK COLLECT requires a collection variable.
This is a FAQ, I think you can find the answer in the topics of these last 2 weeks.

In addition, always use SQL*Plus and copy and paste your session, the WHOLE session,
to show us what you do and get.

Regards
Michel
Re: PLS-00497: cannot mix between single row and multi-row (BULK) in [message #546785 is a reply to message #546704] Thu, 08 March 2012 21:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8804
Registered: November 2002
Location: California, USA
Senior Member
You need to either fetch into a single row variable without bulk collect or limit or fetch with bulk collect and limit into a multi-row variable. Please see the reproduction of problem, followed by options below.

-- reproduction of problem:
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE sp_final_lvl_idx_mon_hub
  2  AS
  3    CURSOR cur_lvl_idx_mon IS
  4    SELECT emp.ename, dept.dname
  5    FROM   emp, dept
  6    WHERE  emp.deptno = dept.deptno;
  7    cur_lvl_idx_rec cur_lvl_idx_mon%ROWTYPE;
  8  BEGIN
  9    OPEN cur_lvl_idx_mon;
 10    LOOP
 11  	 FETCH cur_lvl_idx_mon BULK COLLECT
 12  	 INTO cur_lvl_idx_rec LIMIT 5;
 13  	 EXIT WHEN cur_lvl_idx_mon%NOTFOUND;
 14    END LOOP;
 15    CLOSE cur_lvl_idx_mon;
 16  END sp_final_lvl_idx_mon_hub;
 17  /

Warning: Procedure created with compilation errors.

SCOTT@orcl_11gR2> show errors
Errors for PROCEDURE SP_FINAL_LVL_IDX_MON_HUB:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/10    PLS-00497: cannot mix between single row and multi-row (BULK) in
         INTO list


-- fetch without bulk collect or limit into single row variable:
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE sp_final_lvl_idx_mon_hub
  2  AS
  3    CURSOR cur_lvl_idx_mon IS
  4    SELECT emp.ename, dept.dname
  5    FROM   emp, dept
  6    WHERE  emp.deptno = dept.deptno;
  7    cur_lvl_idx_rec cur_lvl_idx_mon%ROWTYPE;
  8  BEGIN
  9    OPEN cur_lvl_idx_mon;
 10    LOOP
 11  	 FETCH cur_lvl_idx_mon
 12  	 INTO cur_lvl_idx_rec;
 13  	 EXIT WHEN cur_lvl_idx_mon%NOTFOUND;
 14    END LOOP;
 15    CLOSE cur_lvl_idx_mon;
 16  END sp_final_lvl_idx_mon_hub;
 17  /

Procedure created.

SCOTT@orcl_11gR2> show errors
No errors.


-- fetch with bulk collect and limit into multi-row variable:
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE sp_final_lvl_idx_mon_hub
  2  AS
  3    CURSOR cur_lvl_idx_mon IS
  4    SELECT emp.ename, dept.dname
  5    FROM   emp, dept
  6    WHERE  emp.deptno = dept.deptno;
  7    type cur_lvl_idx_tab is table of cur_lvl_idx_mon%ROWTYPE;
  8    v_cur_lvl_idx_tab  cur_lvl_idx_tab;
  9  BEGIN
 10    OPEN cur_lvl_idx_mon;
 11    LOOP
 12  	 FETCH cur_lvl_idx_mon BULK COLLECT
 13  	 INTO v_cur_lvl_idx_tab LIMIT 5;
 14  	 EXIT WHEN cur_lvl_idx_mon%NOTFOUND;
 15    END LOOP;
 16    CLOSE cur_lvl_idx_mon;
 17  END sp_final_lvl_idx_mon_hub;
 18  /

Procedure created.

SCOTT@orcl_11gR2> show errors
No errors.

Re: PLS-00497: cannot mix between single row and multi-row (BULK) in [message #662296 is a reply to message #546785] Mon, 24 April 2017 03:45 Go to previous messageGo to next message
mape
Messages: 243
Registered: July 2006
Location: Slovakia
Senior Member

And If I would like write ename from cursor into DBMS_OUTPUT.PUT_LINE,
how to does it ??

DBMS_OUTPUT.PUT_LINE(....);


Thanks
Re: PLS-00497: cannot mix between single row and multi-row (BULK) in [message #662305 is a reply to message #546785] Mon, 24 April 2017 06:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2704
Registered: January 2010
Location: Connecticut, USA
Senior Member
Barbara, your LIMIT example is bad. In most cases it will miss to process last batch of rows. Most people assume %NOTFOUND means there are no more rows to fetch which isn't true. It means number of requested rows can't be fetched. Therefore, your example will exit without processing last batch if number of rows returned by the cursor isn't equally divisible by bulk collect limit:

DECLARE
    CURSOR cur_lvl_idx_mon IS
    SELECT emp.ename, dept.dname
    FROM   emp, dept
    WHERE  emp.deptno = dept.deptno;
    type cur_lvl_idx_tab is table of cur_lvl_idx_mon%ROWTYPE;
    v_cur_lvl_idx_tab  cur_lvl_idx_tab;
BEGIN
    OPEN cur_lvl_idx_mon;
    LOOP
  	 FETCH cur_lvl_idx_mon BULK COLLECT
  	 INTO v_cur_lvl_idx_tab LIMIT 5;
  	 EXIT WHEN cur_lvl_idx_mon%NOTFOUND;
         FOR v_i IN 1..v_cur_lvl_idx_tab.count LOOP
           DBMS_OUTPUT.PUT_LINE(v_cur_lvl_idx_tab(v_i).ename || ' - ' || v_cur_lvl_idx_tab(v_i).dname);
         END LOOp;
    END LOOP;
    CLOSE cur_lvl_idx_mon;
END;
/
CLARK - ACCOUNTING
KING - ACCOUNTING
MILLER - ACCOUNTING
JONES - RESEARCH
FORD - RESEARCH
ADAMS - RESEARCH
SMITH - RESEARCH
SCOTT - RESEARCH
WARD - SALES
TURNER - SALES

PL/SQL procedure successfully completed.

SQL> 

As you can see, we missed to process last batch since it has less than 5 rows. Compare:

DECLARE
    CURSOR cur_lvl_idx_mon IS
    SELECT emp.ename, dept.dname
    FROM   emp, dept
    WHERE  emp.deptno = dept.deptno;
    type cur_lvl_idx_tab is table of cur_lvl_idx_mon%ROWTYPE;
    v_cur_lvl_idx_tab  cur_lvl_idx_tab;
BEGIN
    OPEN cur_lvl_idx_mon;
    LOOP
  	 FETCH cur_lvl_idx_mon BULK COLLECT
  	 INTO v_cur_lvl_idx_tab LIMIT 5;
  	 EXIT WHEN v_cur_lvl_idx_tab.count = 0;
         FOR v_i IN 1..v_cur_lvl_idx_tab.count LOOP
           DBMS_OUTPUT.PUT_LINE(v_cur_lvl_idx_tab(v_i).ename || ' - ' || v_cur_lvl_idx_tab(v_i).dname);
         END LOOp;
    END LOOP;
    CLOSE cur_lvl_idx_mon;
END;
/
CLARK - ACCOUNTING
KING - ACCOUNTING
MILLER - ACCOUNTING
JONES - RESEARCH
FORD - RESEARCH
ADAMS - RESEARCH
SMITH - RESEARCH
SCOTT - RESEARCH
WARD - SALES
TURNER - SALES
ALLEN - SALES
JAMES - SALES
BLAKE - SALES
MARTIN - SALES

PL/SQL procedure successfully completed.

SQL> 

SY.
Re: PLS-00497: cannot mix between single row and multi-row (BULK) in [message #662308 is a reply to message #662305] Mon, 24 April 2017 08:10 Go to previous messageGo to next message
mape
Messages: 243
Registered: July 2006
Location: Slovakia
Senior Member
Thanks a lot.
Re: PLS-00497: cannot mix between single row and multi-row (BULK) in [message #662318 is a reply to message #662305] Mon, 24 April 2017 15:40 Go to previous message
Barbara Boehmer
Messages: 8804
Registered: November 2002
Location: California, USA
Senior Member
Solomon,

Thanks for pointing out the error and providing the correction.

Barbara
Previous Topic: ORA-00054 RESOURCE BUSY AND ACQUIRE WITH NOWAIT
Next Topic: SQL Tunning - Please Help Me
Goto Forum:
  


Current Time: Thu Feb 22 14:48:28 CST 2018

Total time taken to generate the page: 0.03245 seconds