/************************************************************************
* Handling Exceptions in Bulk Operations *
* Documented on 29-SEP-14 04.35.35.980894 PM +05:30 *
* Document By : Murugappan Annamalai *
* Reference : http://www.dba-oracle.com/plsql/t_plsql_exceptions.htm *
************************************************************************/
CREATE TABLE bulk_tb (ran_num NUMBER NOT NULL);
--inserting data using bulk collect
DECLARE
TYPE num_data_typ IS TABLE OF bulk_tb.ran_num%TYPE;
v_dat num_data_typ := num_data_typ();
BEGIN
FOR i in 1..200
LOOP
v_dat.EXTEND;
v_dat(v_dat.LAST) := i;
END LOOP;
FORALL i IN v_dat.FIRST..v_dat.LAST
INSERT INTO bulk_tb VALUES(v_dat(i));
commit;
END;
SELECT COUNT(*) FROM bulk_tb;
COUNT(*)
-------
200
TRUNCATE TABLE bulk_tb;
sample2.sql --without exception part
DECLARE
TYPE num_data_typ IS TABLE OF bulk_tb.ran_num%TYPE;
v_dat num_data_typ := num_data_typ();
BEGIN
FOR i in 1..200
LOOP
v_dat.EXTEND;
v_dat(v_dat.LAST) := i;
END LOOP;
v_dat(100) := NULL;
/* will cause error while inserting data into bulk_tb
because of not null constraint */
FORALL i IN v_dat.FIRST..v_dat.LAST
INSERT INTO bulk_tb VALUES(v_dat(i));
commit;
END;
/*
Error Message :
ORA-01400: cannot insert NULL into ("HR"."bulk_tb"."ran_num")
ORA-06512: at line 15
*/
sample2.sql --with exception part
DECLARE
TYPE num_data_typ IS TABLE OF bulk_tb.ran_num%TYPE;
v_dat num_data_typ := num_data_typ();
BEGIN
FOR i in 1..200
LOOP
v_dat.EXTEND;
v_dat(v_dat.LAST) := i;
END LOOP;
v_dat(100) := NULL;
/* will cause error while inserting data into bulk_tb
because of not null constraint */
BEGIN
FORALL i IN v_dat.FIRST..v_dat.LAST
INSERT INTO bulk_tb VALUES(v_dat(i));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error while inserting bulk record '||SQLERRM);
END;
END;
SELECT COUNT(*) FROM bulk_tb;
COUNT(*)
-------
99
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
Holds the iteration (not the subscript) of the original FORALL statement that raised the exception.
In sparsely populated collections,
the exception row must be found by looping through the original collection the correct number of times.
SQL%BULK_EXCEPTIONS(i).ERROR_CODE
Holds the exceptions error code.
The total number of exceptions can be returned using the collections COUNT method,
which returns zero if no exceptions were raised. The save_exceptions.sql script,
a modified version of the handled_exception.sql script, demonstrates this functionality.
DECLARE
TYPE num_data_typ IS TABLE OF bulk_tb.ran_num%TYPE;
v_dat num_data_typ := num_data_typ();
v_ex_count NUMBER(4);
abort_ex EXCEPTION;
PRAGMA EXCEPTION_INIT(abort_ex, -24381);
BEGIN
FOR i in 1..200
LOOP
v_dat.EXTEND;
v_dat(v_dat.LAST) := i;
END LOOP;
v_dat(100) := NULL;
v_dat(150) := NULL;
/* will cause error while inserting data into bulk_tb
because of not null constraint */
EXECUTE IMMEDIATE 'TRUNCATE TABLE bulk_tb';
BEGIN
FORALL i IN v_dat.FIRST..v_dat.LAST SAVE EXCEPTIONS
INSERT INTO bulk_tb VALUES(v_dat(i));
COMMIT;
EXCEPTION
WHEN abort_ex THEN
v_ex_count := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1..v_ex_count LOOP
dbms_output.put_line('Error: ' || i ||' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
' Message: ' || SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
END;
/*
Sample output:
Error: 1 Array Index: 100 Message: -1400: non-ORACLE exception
Error: 2 Array Index: 150 Message: -1400: non-ORACLE exception
*/
SELECT COUNT(*) FROM bulk_tb;
COUNT(*)
-------
198
SAVE EXCEPTIONS clause being removed, in the above script now traps a different error number.
The output from this script is listed below.
/*
Sample output:
Error: 1 Array Index: 100 Message: -1400: non-ORACLE exception
*/
SELECT COUNT(*) FROM bulk_tb;
COUNT(*)
-------
99
SELECT COUNT(*) FROM bulk_tb;
DROP TABLE bulk_tb;
/
