Monday, August 8, 2016

Template to Use SAVE EXCEPTIONS in COLLECTIONS (Oracle Pl/SQL table Types)

DECLARE -- Oracle9i and above!
  l_array   <array_type_declaration>;
  bulk_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 );
BEGIN   
   FORALL indx IN l_array.FIRST .. l_array.LAST
      SAVE EXCEPTIONS
      /*DML statement of choice*/
      |;
EXCEPTION
   WHEN bulk_errors
   THEN
      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
      LOOP
         DBMS_OUTPUT.PUT_LINE (
            'Error ' || indx || ' occurred during ' ||
            'iteration ' || SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX ||
            ' updating name to ' ||SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX);
         DBMS_OUTPUT.PUT_LINE (
            'Oracle error is ' ||
            SQLERRM(-1 * SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));
      END LOOP;  
END;
/


--- Other way 

DECLARE -- Oracle9i and above!
  l_array   <array_type_declaration>;
BEGIN    
   FORALL indx IN l_array.FIRST .. l_array.LAST
      SAVE EXCEPTIONS
      /*DML statement of choice*/
      |;
EXCEPTION
   WHEN OTHERS
   THEN 
               IF SQLCODE = -24381
               THEN
      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
      LOOP
         DBMS_OUTPUT.PUT_LINE (
            'Error ' || indx || ' occurred during ' ||
            'iteration ' || SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX ||
            ' updating name to ' ||SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX);
         DBMS_OUTPUT.PUT_LINE (
            'Oracle error is ' ||
            SQLERRM(-1 * SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));
      END LOOP;   
           ELSE
               RAISE;
           END IF;
END;
/

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.