Handling PL/SQL Errors

Using the error handling capability of the Stored Procedures stage, you can check for Oracle-specific database errors, define your own error codes, or do both. The PL/SQL call RAISE_APPLICATION_ERROR() returns user-defined or Oracle database status to the Stored Procedures stage. The following PL/SQL fragment catches various error states:

EXCEPTION
WHEN NO_DATA_FOUND THEN
   RAISE_APPLICATION_ERROR (-20001,'No data found - just warning');
WHEN ZERO_DIVIDE THEN
   /* return Oracle database error (ORA-01476 and message, treat as warning*/
WHEN OTHERS THEN
   RAISE_APPICATION_ERROR (-20002,"SOMETHING BAD HAPPENED - ABORT');

Provide the following values for User defined errors on the Error Codes tab of the Stage page:

Fatal errors: 20002
Warnings: 20001 01476

You can enter multiple error codes in each category. Separate them with a space.

In this example, NO DATA FOUND and ZERO DIVIDE are treated as warnings, the job continues to run, and the stage processes subsequent rows. All other errors are fatal and cause the job to abort.

If you select Procedure status to link on the General tab of the Output page, RAISE_APPLICATION_ERROR() does a rollback on pending rows. The error codes and messages from RAISE_APPLICATION_ERROR() are passed down the output link as the first two columns.