Systems Engineering and RDBMS

Archive for June 19th, 2007

Bulk Collect and NO_DATA_FOUND exception

Posted by decipherinfosys on June 19, 2007

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.

Posted in Oracle | 3 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 84 other followers