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: )