PLS-00497: cannot mix between single row and multi-row (BULK) in [message #546703] |
Thu, 08 March 2012 05:14 |
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 #662305 is a reply to message #546785] |
Mon, 24 April 2017 06:08 |
Solomon Yakobson
Messages: 3275 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.
|
|
|
|
|