Save Exceptions in Oracle

This article explains how to use save exceptions in Oracle. 

Within any simple block or stored procedure while parsing the records, due to some business logic suppose it found few intermediate records as invalid – for those records the exceptions – are saved into the sql%bulk_exceptions array, whereas all valid rows are still processed. 

This sql%bulk_exceptions array stores the details for each invalid row, which contains an ERROR_INDEX(iteration number on FORALL statement) and an ERROR_CODE(Oracle error code).

Example:

Create emp table with data. as per demobld.sql

CREATE TABLE emp3(ename VARCHAR2(5), empno NUMBER(4), sal NUMBER(7,2));

declare

   sv_excep EXCEPTION;

   PRAGMA EXCEPTION_INIT(sv_excep, -24381);

   cursor cur is select * from emp;

   type emp_typ is table of emp%ROWTYPE;

   emp_tbl emp_typ := emp_typ();

begin

   open cur;

   fetch cur bulk collect into emp_tbl;

   forall i in emp_tbl.first .. emp_tbl.last save exceptions

      insert into emp3 values(emp_tbl(i).ename, emp_tbl(i).empno, emp_tbl(i).sal);

   close cur;

exception

   when sv_excep then

      for i in 1..SQL%BULK_EXCEPTIONS.COUNT loop

         DBMS_OUTPUT.PUT_LINE(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX||’, ‘||SQL%BULK_EXCEPTIONS(i).ERROR_CODE);

      end loop;

end;

/

5, 12899

10, 12899

14, 12899

PL/SQL procedure successfully completed.

 

Here in this case 5, 10, 14 are the ERROR_INDEX and ORA-12899 is the ERROR_CODE.

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn