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.

CREATE TABLE bulk_rowcount_test AS
SELECT *
FROM   all_users;

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>