Unanswered: Does any exception raised with Bulk Collect!!
I am having a question regarding the bulk collect. I am using bulk collect in my SELECT statement. when SELECT statement does not retrive any statement [ when it uses bulk collect], it is not raising any exception. But when we normally do a select statement from a table and if the select does not retrive any rows , then it will raise an exception [in PLSQL]. So y is this happening and y is it not raising any exception if select count is zero.
Or is there any situation will this bulk collect will raise an exception? If so , then what is that situation ? To handle the exception, do i need to use BULK_EXCEPTION?
Example Query would be :
Select emp_name bulk collect into lv_name from emp;
Thanks in advance.
Note : i searched through the web and as i am not able to find the appropriate answer, i am posting the same to the Forum.
Because NO_DATA_FOUND is applicable only to single-row selects (as is TOO_MANY_ROWS). Once you use BULK COLLECT you are expecting a variable number of rows, and that exception does not apply. You can check how many records were returned using SQL%ROWCOUNT. If you want, you can then even raise NO_DATA_FOUND:
select ... bulk collect into ... from ...;
if SQL%ROWCOUNT = 0 then
They aren't always the same, although they happen to be the same in this case. Consider:
2 type tabtype is table of number index by binary_integer;
3 tab tabtype;
5 tab(42) := 1;
PL/SQL procedure successfully completed.
There my array has 1 element (tab.count=1), but that element has index value 42, so tab.last=42.
SQL%ROWCOUNT tells you how many rows were processed in the preceding SQL statement. It can be used whether you are using arrays or not.
Also, let me add that one of the advantage of using bulk collections in PL/SQL is also the ability to catch the exceptions into BULKS, without having to actually process them row per row, you rather process then into BULKS as well. Regullary, what you would do is to *ignore* the exceptions, saving them for later auditing when your bulk process finishes.
WHEN others THEN
error_count := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE('Number of errors is ' ||
FOR indx IN 1..error_count LOOP
DBMS_OUTPUT.PUT_LINE('Error ' || indx || '
occurred during '||'iteration ' ||
DBMS_OUTPUT.PUT_LINE('Error is ' ||
for i in 1..SQL%BULK_EXCEPTIONS.COUNT
dbms_output.put_line('Error number ' || i || ' at iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Error message is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));