Bulk collect and Rowcount (SQL%BULK_ROWCOUNT)

In one of our previous blog post, we talked about how to handle NO_DATA_FOUND exception when we are using BULK COLLECT.  In yet another blog post, we also discussed about handling multiple exceptions in BULK COLLECT scenario.

We know that, to loop through records collected using bulk collect, we have to use the FORALL clause.  This blog post, we will see the mechanism to know the number of rows affected by the FORALL statement using SQL%BULK_ROWCOUNT attribute.  SQL cursor has one more attribute %BULK_ROWCOUNT on top of its regular attributes SQL%ISOPEN, SQL%FOUND, SQL%ROWCOUNT etc. While later attributes give information about last executed DML statement, Bulk attribute is used with FORALL statement and SQL%BULK_ROWCOUNT(i) gives the number of rows processed by ith iteration of any DML statement. This is because FORALL statement and SQL%BULK_ROWCOUNT attribute has the same number of elements or subscripts. So if we are bulk collecting 100 records, FORALL will loop through 1 to 100 and so is SQL%BULK_ROWCOUNT attribute. Let us go through an example.

Connect to SQL*Plus with proper credentials and run following query to create the table and populate it with some data.
CREATE TABLE TEST
(
Test_ID NUMBER(9) NOT NULL PRIMARY KEY,
Test_Desc VARCHAR(50),
CREATE_DATE DATE
)
/

INSERT INTO TEST(TEST_ID, TEST_DESC)
SELECT ROWNUM,OBJECT_TYPE
FROM USER_OBJECTS
/

COMMIT
/

10 rows created.

Run following PL/SQL block to see the rowcount affected by FORALL statement for an update.

DECLARE

TYPE ga_Test_DESC IS TABLE OF TEST.TEST_DESC%TYPE;

va_Test_DESC ga_Test_DESC;

BEGIN

SELECT DISTINCT Test_DESC
BULK COLLECT INTO va_Test_DESC
FROM Test;

FORALL i IN 1..VA_TEST_DESC.COUNT
UPDATE TEST
SET CREATE_DATE = SYSDATE
WHERE TEST_DESC = VA_TEST_DESC(i);

FOR i IN 1..VA_TEST_DESC.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(‘Records updated = ‘ || SQL%BULK_ROWCOUNT(i));
END LOOP;

END;
/

Here is the result set.

Records updated = 1
Records updated = 1
Records updated = 4
Records updated = 4

In our table, we have 4 records each with test_desc value as table and index and 1 record each for trigger and procedure. Since we are updating record using TEST_DESC column, output shows rows updated by an individual iteration. One drawback we can think of is that we know about rows affected by each iteration only after FORALL execution is complete which may not be the desired behavior for certain applications.