Bulk Collect clause is used to return multiple values to collection variables. When data involved is very large, we can use Bulk Collect clause to fetch the data into local PL/SQL variables faster without looping through one record at a time. We can store the result set into either individual collection variables, if we are fetching certain number of columns or collection records, if we are fetching all the columns of the table.
When we are bulk collecting records, if a query does not fetch any records, it does not throw NO_DATA_FOUND exception. We need to check whether the collection variable has any elements or not. In simple words, each record fetched is considered as one element. Let us see this by an example. Connect to the database using SQL*Plus with proper authentication. Execute following sql to create a table.
CREATE TABLE TEST
(
Test_ID NUMBER(9) NOT NULL PRIMARY KEY,
Test_Desc VARCHAR(50)
)
/
Now run the following PL/SQL block in which we are collecting data into scalar variables.
SET SERVEROUTPUT ON
DECLARE
V_Test_ID NUMBER(9);
V_Test_Desc VARCHAR(50);
BEGIN
SELECT Test_ID, Test_Desc
INTO v_Test_ID, v_Test_Desc
FROM Test
WHERE Test_ID = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(’Exception: No Records in the Test Table’);
END;
/
When you execute the PL/SQL block shown above, since we don’t have any data, the NO_DATA_FOUND exception will be caught and the following result will be displayed.
Exception: No Records in the Test Table
PL/SQL procedure successfully completed.
Now, let’s change the above PL/SQL block to use bulk collect syntax. Execute the following PL/SQL block. As we mentioned earlier, normally bulk collect clause is used when we want to operate on larger data set, but in our example, we are just fetching single record to demonstrate the issue and the resolution.
SET SERVEROUTPUT ON
DECLARE
TYPE ga_Test_ID IS TABLE OF TEST.TEST_Id%TYPE;
TYPE ga_Test_Desc IS TABLE OF TEST.TEST_DESC%TYPE;
va_Test_ID ga_Test_ID;
va_Test_Desc ga_Test_Desc;
BEGIN
SELECT Test_ID, Test_Desc
BULK COLLECT INTO va_Test_ID, va_Test_Desc
FROM Test
WHERE Test_ID = 1;
IF va_test_ID.count = 0 THEN
dbms_output.put_line(’Bulk Collect: No Records in the Test Table’);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(’Exception: No Records in the Test Table’);
END;
/
Here is the result of the execution.
Bulk Collect: No Records in the Test Table
PL/SQL procedure successfully completed.
If you look at the message above, you will notice that, it did not generate NO_DATA_FOUND exception so text in the exception is not displayed. Instead we checked for count of elements in the collection and if the count is 0, a different text is displayed.
So, the point to keep in mind is that whenever Bulk Collect clause is used in stored procedures/ functions, checking for NO_DATA_FOUND exception can give wrong results if the query does not return any record.
