Register now or log in to join your professional community.
We can use SAVE EXCEPTIONS to handle the bulk exceptions.The purpose of save exceptions is logging the error into error logging table and if you want to commit those inserted data you can commit itotherwise rollback it based on the count of error logging table.Here is an example of SAVE EXCEPTIONS in bulk operation
Create Sample Original Table:
-----------------------------
SQL> CREATE TABLE ann_tab(id1 NUMBER(10) NOT NULL,
2 ename VARCHAR2(20)
3 );
Table created.
-----------------------------------------------------------------
Create Sample error logging table
--------------------------
SQL> CREATE TABLE err_log(record_no NUMBER(10),
2 err_msg VARCHAR2(200),
3 ename VARCHAR2(20)
4 );
Table created.
------------------------------------------------------------------
Without Save Exceptions we cant able to get the error record information. We are getting the error message and everything got rollback.
SQL> DECLARE
2 TYPE ann_nest IS TABLE OF ann_tab%ROWTYPE;
3 v_annnest ann_nest := ann_nest();
4 BEGIN
5 SELECT id1,ename
6 BULK COLLECT INTO v_annnest
7 FROM (SELECT1 id1,'ANN1' ename FROM dual
8 UNION ALL
9 SELECT2 id1,'ANN2' ename FROM dual
10 UNION ALL
11 SELECT NULL id1,'ANN3' ename FROM dual
12 );
13 FORALL i IN1..v_annnest.COUNT
14 INSERT INTO ann_tab(id1,ename)
15 VALUES(v_annnest(i).id1,v_annnest(i).ename);
16 COMMIT;
17 EXCEPTION
18 WHEN OTHERS THEN
19 RAISE;
20 END;
21 /
DECLARE*ERROR at line1:ORA-01400: cannot insert NULL into ("SCOTT"."ANN_TAB"."ID1")ORA-06512: at line19
---------------------------------------------------------------------------------------------
Check the table count everything rolledback
SQL> SELECT COUNT(1) FROM ann_tab; COUNT(1)
----------
0
----------------------------------------------------------------------------------------------
With Save exception option to save the error record information into err_log table and rolled back the original insert
SQL> DECLARE
2 TYPE ann_nest IS TABLE OF ann_tab%ROWTYPE;
3 v_annnest ann_nest := ann_nest();
4 l_error_count NUMBER;
5 l_error_idx NUMBER;
6 l_ename VARCHAR2(20);
7 l_error_msg VARCHAR2(1000);
------ User defined exception name
8 dml_errors EXCEPTION;
----- Mapping predefined exception to user defined name
9 PRAGMA EXCEPTION_INIT(dml_errors,-24381);
10 BEGIN
11 SELECT id1,ename
12 BULK COLLECT INTO v_annnest
13 FROM (SELECT1 id1,'ANN1' ename FROM dual
14 UNION ALL
15 SELECT2 id1,'ANN2' ename FROM dual
16 UNION ALL
17 SELECT NULL id1,'ANN3' ename FROM dual
18 );
19 BEGIN
------- Forall with save exceptions
20 FORALL i IN1..v_annnest.COUNT SAVE EXCEPTIONS
21 INSERT INTO ann_tab(id1,ename)
22 VALUES(v_annnest(i).id1,v_annnest(i).ename);
23 EXCEPTION ----- Raise user defined exception name if any dml errors occurred
24 WHEN dml_errors THEN
25 FOR j IN1..SQL%BULK_EXCEPTIONS.COUNT
26 LOOP
27 l_error_idx := SQL%BULK_EXCEPTIONS(j).error_index; ----- Collection position
28 l_error_msg := SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE); ---- Error message
29 l_ename := v_annnest(SQL%BULK_EXCEPTIONS(j).error_index).ename; ---- Error data
30 ---- Insert error information into error logging table INSERT INTO err_log(record_no,
31 err_msg,
32 ename
33 )
34 VALUES(l_error_idx,
35 l_error_msg,
36 l_ename
37 );
38 END LOOP;
39 COMMIT;
40 END;
---- Check the count of error logging table
41 SELECT COUNT(1) INTO l_error_count
42 FROM err_log;
---- If error occurred rollback else commit
43 IF (l_error_count >0) THEN
44 ROLLBACK;
45 ELSE
46 COMMIT;
47 END IF;
48 EXCEPTION
49 WHEN OTHERS THEN
50 RAISE;
51 END;
52 /PL/SQL procedure successfully completed.---------------------------------------------------------------------------------------------------------
Check the error logging table data
SELECT * FROM err_log;
RECORD_NO ERR_MSG ENAME
--------- ------------------------------------- -------
3 ORA-01400: cannot insert NULL into () ANN3
-----------------------------------------------------------------------------------------------------------
