using SQL%BULK_ROWCOUNT and SQL%ROWCOUNT : Cursor Attributes « Cursor « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Cursor
3. Cursor Attributes

SQL>
SQL>
SQL> CREATE TABLE customer_region(
  2     region_id    NUMBER(4)    PRIMARY KEY,
  3     region_name  VARCHAR2(11) NOT NULL
  4  );

Table created.

SQL>
SQL>
SQL> INSERT INTO customer_region VALUES (1,'REGION1');

1 row created.

SQL> INSERT INTO customer_region VALUES (2,'REGION2');

1 row created.

SQL> INSERT INTO customer_region VALUES (3,'REGION3');

1 row created.

SQL> INSERT INTO customer_region VALUES (4,'REGION4');

1 row created.

SQL>
SQL>
SQL> DECLARE
  2    Type regionIdTableCollection IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
  3    Type regionNameTableCollection IS TABLE of VARCHAR2(20)INDEX BY BINARY_INTEGER;
  4    region_ids regionIdTableCollection;
  5    region_names regionNameTableCollection;
  6    returnCode NUMBER;
  7    errorMessage VARCHAR2(1000);
  8    Procedure load_regions_bulk_bind
  9          (region_ids IN regionIdTableCollection,
 10           region_names IN regionNameTableCollection,
 11           returnCode OUT NUMBER,
 12           errorMessage OUT VARCHAR2)
 13    Is
 14    BEGIN
 15      FORALL i IN region_ids.FIRST..region_ids.LAST
 16        INSERT INTO customer_region values (region_ids(i),region_names(i));
 17      FOR i in 1..region_ids.COUNT LOOP
 18        IF SQL%BULK_ROWCOUNT(i)>0 THEN
 19          dbms_output.put_line(to_char(sql%bulk_rowcount(i)));
 20          NULL;
 21        END IF;
 22      END LOOP;
 23      IF SQL%ROWCOUNT =0 THEN
 24        DBMS_OUTPUT.PUT_LINE('No Rows inserted overall');
 25      ELSE
 26        COMMIT;
 27      END IF;
 28    EXCEPTION WHEN OTHERS THEN
 29      COMMIT;
 30      returnCode :=SQLCODE;
 31      errorMessage :=SQLERRM;
 32    END;
 33  BEGIN
 34    region_ids(1):=6;
 35    region_names(1):='region6';
 36    load_regions_bulk_bind(region_ids,region_names,returnCode,errorMessage);
 37  END;
 38  /
1

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table customer_region;

Table dropped.

SQL>