ORACLE-FORU - SQL%BULK_ROWCOUNT

SQL%BULK_ROWCOUNT

The SQL%BULK_ROWCOUNT cursor attribute gives granular information about the rows affected by each iteration of the FORALL statement. Every row in the driving collection has a corresponding row in the SQL%BULK_ROWCOUNT cursor attribute.

Lets see example you will clearly understand.

create one table.

CREATE TABLE bulk_rowcount_test AS

SELECT *

FROM   all_users;

Below example will clearly understand how it will work through sql%bulk_rowcount.

SET SERVEROUTPUT ON

DECLARE

  TYPE t_array_tab IS TABLE OF VARCHAR2(30);

  l_array t_array_tab := t_array_tab('SCOTT', 'SYS',

                                     'SYSTEM', 'DBSNMP', 'BANANA'); 

BEGIN

  -- Perform bulk delete operation.

  FORALL i IN l_array.first .. l_array.last 

    DELETE FROM bulk_rowcount_test

    WHERE username = l_array(i);

  -- Report affected rows.

  FOR i IN l_array.first .. l_array.last LOOP

    DBMS_OUTPUT.put_line('Element: ' || RPAD(l_array(i), 15, ' ') ||

      ' Rows affected: ' || SQL%BULK_ROWCOUNT(i));

  END LOOP;

END;

/

Element: SCOTT           Rows affected: 1

Element: SYS             Rows affected: 1

Element: SYSTEM          Rows affected: 1

Element: DBSNMP          Rows affected: 1

Element: BANANA          Rows affected: 0

PL/SQL procedure successfully completed.

SQL>

So we can see that no rows were deleted when we performed a delete for the username "BANANA".