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 as 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