Bulk Collect NO_DATA_FOUND

Bulk collect clause do not generate an error when it do not find data in Select query. We need to check whether collection variable has any element or not.

Sample:

create table test_bulk (cola number, colb number) ;
/
Table created.

declare
TYPE tNum IS TABLE OF NUMBER ;
a tNum;
b tNum;
BEGIN
SELECT cola, colb
BULK COLLECT INTO a, b
FROM test_bulk ;
IF a.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('BULK COLLECT: NO DATA FOUND') ;
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION: NO DATA FOUND') ;
END ;
/
BULK COLLECT: NO DATA FOUND
PL/SQL procedure successfully completed.