Hi,
I'm using an 11.2.0.4 db and have recently noticed that the following 'no_data_found' issue has occurred when exiting a bulk collect
operation that has 0 rows left to process.
N.B. I have read on many sites that bulk collect doesn't generate a 'no_data_found' error so would be very grateful if someone could
assist with this issue:
The Bulk Collect operation has 86 rows to process.
The code has been developed by someone else previously.
DECLARE
CURSOR bmu_cur IS -- returns 86 rows
SELECT col1
,col2
FROM table1
GROUP BY col1
,col2
ORDER BY 1,2;
TYPE bmu_tab IS TABLE OF bmu_cur%ROWTYPE INDEX BY PLS_INTEGER;
t_bmu_cur bmu_tab;
t_bmu bmu_tab;
BEGIN
OPEN bmu_cur;
--
LOOP
dbms_output.put_line('Just before FETCH bmu_cur cursor');
FETCH bmu_cur
BULK COLLECT INTO t_bmu_cur
LIMIT 100;
dbms_output.put_line('About to check for EXIT COUNT - count is: '||t_bmu_cur.COUNT);
EXIT WHEN t_bmu_cur.COUNT = 0;
--
dbms_output.put_line('Just before FOR LOOP t_bmu_cur');
FOR i IN 1 .. t_bmu_cur.COUNT
LOOP
-- do processing
t_bmu(t_bmu.COUNT + 1) := t_bmu_cur(i);
END LOOP;
--
dbms_output.put_line('Just after FOR LOOP t_bmu_cur');
END LOOP;
--
CLOSE bmu_cur;
EXCEPTION
WHEN OTHERS THEN
-- Remove code after resolved
IF bmu_cur%ISOPEN THEN
CLOSE bmu_cur;
END IF;
dbms_output.put_line('Error occurred in Update BMU: '||sqlerrm);
END;
Output as follows:
Just before FETCH bmu_cur cursor
About to check for EXIT COUNT - count is: 86
Just before FOR LOOP t_bmu_cur
Just after FOR LOOP t_bmu_cur
Just before FETCH bmu_cur cursor
About to check for EXIT COUNT - count is: 0
Error occurred in Update BMU: ORA-01403: no data found
Can someone please explain the reason for this and how best to deal with it?
I could potentially explicitly handle the exception but want to know what has generated it?
Your assistance is much appreciated.
Kind regards,
Tom