Discussion in 'SQL PL/SQL' started by ramprasad.cgs, Dec 6, 2014.
can anyone tell me why bulk collect will not raise in no_data_found? explain
For bulk operations (FORALL, BULK COLLECT) you need to use the SAVE EXCEPTIONS clause and BULK_EXECEPTIONS.
Google bulk collect exceptions to get more details.
This is a documented Oracle behavior:
Refer to following link for more info:
In order to check if "no data found" (i.e. no rows returned), check the content of the collection after the SELECT ... BULK COLLECT INTO or FETCH ... BULK COLLECT INTO .. statements using " IF <my_collection>.COUNT = 0...."
The SELECT INTO statement with the BULK COLLECT clause (also called the SELECT BULK COLLECT INTO statement) selects an entire result set into one or more collection variables.
BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval
SELECT BULK COLLECT INTO Statements
Reducing Loop Overhead for DML Statements and Queries with Bulk SQL
Bulk collect fetches groups of rows at a time; the NO_DATA_FOUND is embedded in the last fetch and it IS a trigger to terminate the loop. Of course when it DOES terminate the loop the last, incomplete group of records fetched is 'lost':
The second example proves this. It also shows how to get around the 'problem' and return all of the fetched data.