Monday, September 29, 2014

BULK Exceptions



/************************************************************************
*   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;
/


1 comment: