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> 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> 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> drop table customer_region;
Table dropped.
SQL>