BULK_EXCEPTIONS

SQL%BULK_EXCEPTIONS

SQL%BULK_EXCEPTIONS argument saves all the exceptions raised during the bulk operation provided FORALL statement should have SAVE EXCEPTIONS added.

SQL%BULK_EXCEPTIONS.count gives the number of exceptions raised in the last FORALL bulk opearation

SQL%BULK_EXCEPTIONS.ERROR_INDEX gives the iteration number in which the exception is raised

SQL%BULK_EXCEPTIONS.ERROR_CODE gives the error code, using which we can find the error message as ERRM( - SQL%BULK_EXCEPTIONS ).

Sample (Using Emp table)

Without using SAVE EXCEPTIONS

DECLARE

  TYPE larr_typ_emp IS TABLE OF NUMBER;

  larr_emp larr_typ_emp := larr_typ_emp(7782, 7839, 7934, 7566, 7788, 7876,

                                        7369, 7499, 7521, 7844, 7900, 7698,

                                        7654, 7902);

BEGIN

  FORALL i IN larr_emp.FIRST .. larr_emp.LAST

    UPDATE emp SET job = job || 'JOB' WHERE empno = larr_emp(i);

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('Total No of Errors:' ||

                         SQL%BULK_EXCEPTIONS.COUNT);

    FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT

    LOOP

      dbms_output.put_line('index:' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);

      dbms_output.put_line('code:' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE);

      dbms_output.put_line('message (Without -):' ||

                           SQLERRM(sql%BULK_EXCEPTIONS(i).ERROR_CODE));

      dbms_output.put_line('message (With -):' ||

                           SQLERRM(-sql%BULK_EXCEPTIONS(i).ERROR_CODE));

    END LOOP;

END;

/

Out put would be

Total No of Errors:1

index:1

code:12899

message (Without -): -12899: non-ORACLE exception

message (With -):ORA-12899: value too large for column "AISDEV"."EMP"."JOB" (actual: 10, maximum: 9)

Using SAVE EXCEPTIONS

DECLARE

  TYPE larr_typ_emp IS TABLE OF NUMBER;

  larr_emp larr_typ_emp := larr_typ_emp(7782, 7839, 7934, 7566, 7788, 7876,

                                        7369, 7499, 7521, 7844, 7900, 7698,

                                        7654, 7902);

BEGIN

  FORALL i IN larr_emp.FIRST .. larr_emp.LAST SAVE EXCEPTIONS

    UPDATE emp SET job = job || 'JOB' WHERE empno = larr_emp(i);

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('Total No of Errors:' ||

                         SQL%BULK_EXCEPTIONS.COUNT);

    FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT

    LOOP

      dbms_output.put_line('index:' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);

      dbms_output.put_line('code:' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE);

      dbms_output.put_line('message (Without -):' ||

                           SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

      dbms_output.put_line('message (With -):' ||

                           SQLERRM(-sql%BULK_EXCEPTIONS(i).ERROR_CODE));

    END LOOP;

END;

/

Out put would be

Total No of Errors:10

index:1

code:12899

message (Without -): -12899: non-ORACLE exception

message (With -):ORA-12899: value too large for column  (actual: , maximum: )

index:2

code:12899

message (Without -): -12899: non-ORACLE exception

message (With -):ORA-12899: value too large for column  (actual: , maximum: )

index:4

code:12899

message (Without -): -12899: non-ORACLE exception

message (With -):ORA-12899: value too large for column  (actual: , maximum: )

index:5

code:12899

message (Without -): -12899: non-ORACLE exception

message (With -):ORA-12899: value too large for column  (actual: , maximum: )

index:8

code:12899

message (Without -): -12899: non-ORACLE exception

message (With -):ORA-12899: value too large for column  (actual: , maximum: )

index:9

code:12899

message (Without -): -12899: non-ORACLE exception

message (With -):ORA-12899: value too large for column  (actual: , maximum: )

index:10

code:12899

message (Without -): -12899: non-ORACLE exception

message (With -):ORA-12899: value too large for column  (actual: , maximum: )

index:12

code:12899

message (Without -): -12899: non-ORACLE exception

message (With -):ORA-12899: value too large for column  (actual: , maximum: )

index:13

code:12899

message (Without -): -12899: non-ORACLE exception

message (With -):ORA-12899: value too large for column  (actual: , maximum: )

index:14

code:12899

message (Without -): -12899: non-ORACLE exception

message (With -):ORA-12899: value too large for column  (actual: , maximum: )

Bulk Rowcount

Bulk Rowcount

Lets assume, emp table has below data

	EMPNO	ENAME	JOB	MGR	SAL	DEPTNO
1	7782	CLARK	MANAGER	7839	2450.00	10
2	7839	KING	PRESIDENT		5000.00	10
3	7934	MILLER	CLERK	7782	1300.00	10
4	7566	JONES	MANAGER	7839	2975.00	20
5	7788	SCOTT	ANALYST	7566	3000.00	20
6	7876	ADAMS	CLERK	7788	1100.00	20
7	7369	SMITH	CLERK	7902	800.00	20
8	7499	ALLEN	SALESMAN	7698	1600.00	30
9	7521	WARD	SALESMAN	7698	1250.00	30
10	7844	TURNER	SALESMAN	7698	1500.00	30
11	7900	JAMES	CLERK	7698	950.00	30
12	7698	BLAKE	MANAGER	7839	2850.00	30
13	7654	MARTIN	SALESMAN	7698	1250.00	30
14	7902	FORD	ANALYST	7566	3000.00	60

I am writing a script to update data in bulk using a FORALL loop; I also wanted to know the number of rows got updated in each iteration. Using ROWCOUNT, I will know the total count but not for each iteration: See the sample script below

DECLARE

  TYPE larr_typ_dept IS TABLE OF NUMBER;

  larr_dept larr_typ_dept := larr_typ_dept(10, 60);

BEGIN

  FORALL i IN larr_dept.FIRST .. larr_dept.LAST

    UPDATE emp SET sal = sal + 100 WHERE deptno = larr_dept(i);

  dbms_output.put_line('The number of records updated (Count By ROWCOUNT) ' || SQL%ROWCOUNT);

  FOR j IN larr_dept.FIRST .. larr_dept.LAST

  LOOP

    dbms_output.put_line('The number of records updated in ' || j ||

                         'th iteration; For Dept  ' || larr_dept(j) ||

                         ' (Count By BULK_ROWCOUNT) :: ' || SQL%BULK_ROWCOUNT(j));

  END LOOP;

END;

Out put would be

The number of records updated (Count By ROWCOUNT) 4

The number of records updated in 1th iteration; For Dept  10 (Count By BULK_ROWCOUNT) :: 3

The number of records updated in 2th iteration; For Dept  60 (Count By BULK_ROWCOUNT) :: 1