Exceptions and error messages support

Any error that occurs in an NZPLSQL procedure aborts the execution of the procedure, and it aborts the surrounding transaction as well. The system returns to the main loop to obtain the next query from the client application. It is not possible to catch all exceptions, especially if that action leaves the database in a bad state.

To catch and process an error that occurs in a procedure block, you can add an exception-handling section at the end of a block by using the EXCEPTION keyword. If no error occurs, the exception-handling section is ignored. Within the exception-handling section, you can specify one or more exception handlers by using WHEN clauses that specify the exception to match and the statements to run when a match occurs. An EXCEPTION statement has the following form:

EXCEPTION 
    WHEN clause THEN
        statements 
    [ WHEN ... ]

The NZPLSQL supports two clauses for exception processing:

TRANSACTION_ABORTED: Use the TRANSACTION_ABORTED clause to specify the statements that you want to run when an error occurs that causes the transaction to abort. In this case, a ROLLBACK is required to continue. Make sure that the ROLLBACK command is the first command in the exception handling statements.
OTHERS: Use the OTHERS clause to specify the statements to run when any error occurs within the procedure block. Errors such as a SQL parsing error do not abort the transaction, and thus would not match a TRANSACTION_ABORTED clause.

You can specify both clauses in an exception block, as in the following example. When an error occurs in the procedure, the procedure code uses the first matching exception clause and executes the statements in that clause. Any clauses after the match are ignored. As a best practice, specify the OTHERS clause last because it matches any type of error.

EXCEPTION 
    WHEN TRANSACTION_ABORTED THEN
       ROLLBACK;
       statements_case1 
       RAISE ERROR 'Procedure failed: %', sqlerrm;
    WHEN OTHERS THEN
       statements_case2
       RAISE NOTICE 'Caught error, continuing %', sqlerrm;

In this example, an error that aborts a transaction triggers the TRANSACTION_ABORTED clause and the stored procedure executes the ROLLBACK, the statements in statements_case1, and raises the error message. If the error did not cause the transaction to abort, the exception processing skips to the OTHERS clause and the stored procedure runs the statements_case2 set and raises the notice message. If there were no errors in the block, the exception statements are skipped.

The variable SQLERRM contains the text of an error message that is caught. In the absence of an exception block, the exception propagates up to the next stored procedure in the call stack. If sproc1 calls sproc2, which generates an exception, but sproc2 does not have an exception handler, then the system looks for a handler in sproc1. The system also looks at the enclosing block declarations.

For example:

create or replace procedure sp_except01() returns BOOL LANGUAGE 
NZPLSQL AS 
BEGIN_PROC
DECLARE
    r record;
BEGIN
    <<inner>>
    BEGIN
        SELECT * INTO r FROM NONEXISTENT;
    END;
END;
END_PROC;

create or replace procedure sp_except02() returns BOOL LANGUAGE 
NZPLSQL AS 
BEGIN_PROC
BEGIN
    CALL sp_except01();
END;
END_PROC;

create or replace procedure sp_except03() returns BOOL LANGUAGE 
NZPLSQL AS 
BEGIN_PROC
BEGIN
    CALL sp_except02();
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Caught exception';
END;
END_PROC;

In these examples, the exception is generated in sp_except01, in the block inner. The system first checks for an exception handler for block inner, which is not found. Control passes to the parent context, which is the procedure sp_except01, and an exception handler is also not found there. Control then passes to sp_except02, and finally sp_except03, where an exception handler is found and used.

If an exception is not caught at any level, additional NOTICE-level log messages are sent to provide context about the error and where it occurred (line number and type of statement, unless the error is from a RAISE EXCEPTION statement).

If you include exception handlers in stored procedures, the handlers catch any errors and the errors are not displayed. For example:

CREATE PROCEDURE sp() RETURNS INTEGER LANGUAGE NZPLSQL AS
BEGIN_PROC
BEGIN
  EXECUTE IMMEDIATE 'insert into NOTEXIST' || 'values(1,1)';
  EXCEPTION WHEN OTHERS THEN
  END;
END_PROC;

Assuming that NOTEXIST does not exist in the database, the query does not display any error output because the error was handled by the exception handler.

To display the error, write the procedure as follows:

CREATE PROCEDURE sp() RETURNS INTEGER LANGUAGE NZPLSQL AS
BEGIN_PROC
BEGIN
  EXECUTE IMMEDIATE 'insert into NOTEXIST' || ' values(1,1)';
  EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'Got exception: %', SQLERRM;
  END;
END_PROC;

When you run this query, it displays the following output:

NOTICE:  Got exception: ERROR:  Relation 'NOTEXIST' does not exist