Bulk Exception Example

Bulk Exception  

Here is the example of Handeling Bulk Exception in BULK Collect and FOR ALL .

DROP TABLE emp_bulk_exp_tgt PURGE;

DROP TABLE emp_bulk_exp PURGE;

-- 1. Create Source Table without any Constraints 

CREATE TABLE emp_bulk_exp ( 

   emp_id    NUMBER       ,

   emp_name  VARCHAR2(50) ,

   salary    NUMBER       ,

   dept_id   NUMBER

);

CREATE TABLE emp_bulk_exp_tgt (

   emp_id    NUMBER       PRIMARY KEY,

   emp_name  VARCHAR2(50) NOT NULL,

   salary    NUMBER       CONSTRAINT emp_bulk_exp_ck_tgt CHECK (salary >= 0),

   dept_id   NUMBER

);

-- Insert Raw Data in source table 

INSERT INTO emp_bulk_exp VALUES (100, 'Amit',   50000, 10);

INSERT INTO emp_bulk_exp VALUES (101, 'Sumit',   60000, 10);

INSERT INTO emp_bulk_exp VALUES (100, 'Amit',   50000, 10);

INSERT INTO emp_bulk_exp VALUES (102, '',   50000, 10);

COMMIT;

-- 3. Bulk Collect + FORALL with SAVE EXCEPTIONS

SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE

   TYPE emp_tab IS TABLE OF emp_bulk_exp%ROWTYPE;

   l_emps emp_tab;

BEGIN

   -- Bulk fetch all employees

   SELECT * BULK COLLECT INTO l_emps FROM emp_bulk_exp;

   DBMS_OUTPUT.PUT_LINE('Total rows to insert: ' || l_emps.COUNT);

   -- Bulk insert with SAVE EXCEPTIONS

   FORALL i IN 1..l_emps.COUNT SAVE EXCEPTIONS

       INSERT INTO emp_bulk_exp_tgt VALUES l_emps(i);

   COMMIT;  

   DBMS_OUTPUT.PUT_LINE('Bulk insert completed successfully without exception.');

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('Exception encountered ');

    --------------Printing Saved Exceptions -----------------

      FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP

         DBMS_OUTPUT.PUT_LINE(

            '  Error at record #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||

            ' - ORA-' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE || ' : ' ||

            SUBSTR(SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE),1,200)

         );

   --------Printing Record which has exceptions-----

         DBMS_OUTPUT.PUT_LINE(

            '    Error Record  => EMP_ID='   || l_emps(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX).emp_id ||

            ', EMP_NAME=' || NVL(l_emps(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX).emp_name,'<NULL>') ||

            ', SALARY='   || NVL(TO_CHAR(l_emps(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX).salary),'<NULL>') ||

            ', DEPT_ID='  || NVL(TO_CHAR(l_emps(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX).dept_id),'<NULL>')

         );

      END LOOP;

END;

/

-- 4. Check successful inserts

SELECT * FROM emp_bulk_exp_tgt ORDER BY emp_id;

Output -

Table EMP_BULK_EXP_TGT dropped.

Table EMP_BULK_EXP dropped.

Table EMP_BULK_EXP created.

Table EMP_BULK_EXP_TGT created.

1 row inserted.

1 row inserted.

1 row inserted.

1 row inserted.

Commit complete.

Total rows to insert: 4

Exception encountered 

  Error at record #4 - ORA-1400 : ORA-01400: cannot insert NULL into ()

    Error Record  => EMP_ID=102, EMP_NAME=<NULL>, SALARY=50000, DEPT_ID=10

  Error at record #3 - ORA-1 : ORA-00001: unique constraint (.) violated

    Error Record  => EMP_ID=100, EMP_NAME=Amit, SALARY=50000, DEPT_ID=10

PL/SQL procedure successfully completed.

    EMP_ID EMP_NAME         SALARY    DEPT_ID

---------- ------------ ---------- ----------

       100 Amit              50000         10

       101 Sumit             60000         10