Teradata Stored Procedures – Error Handling

1
3850

Teradata Stored Procedures

Overview

Error handling in Teradata Stored Procedures is condition based, having the same functionality offered by modern programming languages (C++, Java, etc.).

Without the availability of state-based error handling one would need to implement and invoke code for each type of error and at each code location where the error could occur.

Not only is this a tremendous effort from a development point of view, but some error handling can easily be forgotten about.

Below you can see one example of such a programmatic approach, handling division by zero errors.

REPLACE PROCEDURE DIVISION_BY_ZERO (IN n INTEGER)
BEGIN
DECLARE x INTEGER DEFAULT 10;
— Error Handling programmatically
CASE
WHEN n = 0 THEN SET x = 0; ELSE SET x = (x/ n);
END CASE;
END;

CALL DIVISION_BY_ZERO(0);

When using error handlers, the handler code for a certain error is defined once within the handler and executed automatically each time the error condition is raised.

Below you see the division by zero problems solved with an error handler. The error code ‘22012’ is returned in case of a division by zero error:

REPLACE PROCEDURE DIVISION_BY_ZERO (IN n INTEGER)
BEGIN
DECLARE x INTEGER DEFAULT 10;
DECLARE EXIT HANDLER FOR SQLSTATE ‘22012’ SET x = 0; — Error Handler

SET x = (x/n);
END;

CALL DIVISION_BY_ZERO(0);

 

It is possible to define error handlers which are responsible for all errors or warnings (generic handlers), they will be executed if a particular error handler does not already cover an error/warning.

The developer does not need to know in advance all possible kind of errors and warning.

Generic handler is usually used to do the cleanup before exiting a stored procedure. See below two examples for generic exception and warning handlers:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET x =0;
DECLARE CONTINUE HANDLER FOR SQLWARNING SET x =0;

Each stored procedure can have exactly

• One generic handler for exceptions (SQLEXCEPTION)
• One generic handler for warnings (SQLWARNING)
• One generic handler in case a cursor does not return any data (NOTFOUND)
• Per code block (BEGIN…END) and error code exactly one specific handler
The same error code can be handled in different code blocks.

Within a stored procedure, all executed SQL statements return a status which can be: completed, warning or exception. Exceptions and warnings can be handled by an error handler.

The status of each executed SQL statement is returned in the read-only variable SQLSTATE. This available is implicitly available in each Stored Procedure usually, is used within generic error handlers to find out further details about an error or warning.

There exists another read-only variable called SQLCODE, but it is obsolete and should not be used anymore.

SQLSTATE consists of 5 characters. The first two characters define the error class. The remaining three characters represent the error.

Many of the error codes returned by Teradata are mapped 1:1 to the SQLSTATE variable (but not all of them). For example, the Teradata error code 3776 is assigned to T3776 (Error class T3 = Teradata 3).

There are two main types of error handlers: Exit handlers and continue handlers.

The Exit Handler

Whenever the execution of exit handler is finished, the normal code execution continues with the block (defined by BEGIN…END) which is surrounding the statement which caused the exception. Apparently, if there is no surrounding block, the stored procedure is terminating, but this is an exception. In general, don’t get fooled by the name “exit handler,” assuming the stored procedure will end. Only the block causing the handler execution is terminated, and the code execution is passed to the surrounding block.

The Continue Handler

The continue handler continues with the next statement after the one which triggered the handler execution.

Raising of conditions

Usually, the following events raise conditions which will trigger handler execution:

• SQL status exception or warning (after executing an SQL statement)

• The SIGNAL statement which is used to raise an exception programmatically

• The RESIGNAL statement passed by a continue handler to the surrounding block (BEGIN…END)

 

Handler Propagation

Handlers can be declared in each data block (BEGIN…END). Different data blocks can handle different errors or even the same errors. In the case of an error, the code blocks are searched for an appropriate handler, starting with the block were the error occurred until the outermost BEGIN…END is reached. If none of the code blocks contains a handler, the stored procedure will exit with an error.

In any case, after executing a continue handler, code execution persists in the data block where the error triggered. In the event of an exit handler, the code block containing the error triggering statement will be terminated, and code execution continues with the outer block (if any).

In case an error is raised within a handler, the handler execution is terminated, and the stored procedure tries to find another handler by continuing searching the data blocks from the innermost to the outermost. Be aware, that an error event in the handler will not try to solve this handler error but the original error which leads to the handler execution!

Below is an example for nested handlers. We nested three handlers for the division by zero condition. Intentionally we cause another division by zero exception (SET x=1/0) in two of the three handlers. Therefore the outermost handler finally is the one who will handle this error:

REPLACE PROCEDURE DIVISION_BY_ZERO ( IN n INTEGER)
BEGIN
   DECLARE x INTEGER DEFAULT 10.00;
   DECLARE CONTINUE HANDLER FOR SQLSTATE ‘22012’ SET x=0; — handles the error
   BEGIN
   DECLARE EXIT HANDLER FOR SQLSTATE ‘22012’ SET x = 1/0;
   BEGIN
       DECLARE EXIT HANDLER FOR SQLSTATE ‘22012’ SET x = 1/0;
       SET x = x / n;
   END;
END; END;

CALL DIVISION_BY_ZERO(0);

Our Reader Score
[Total: 13    Average: 3.7/5]
Teradata Stored Procedures – Error Handling written by Roland Wenzlofsky average rating 3.7/5 - 13 user ratings

1 COMMENT

  1. hello.. I am trying to get exception message which is coming out of it. Is there any way, I can get exact message and log it in ErrorLog table? Below is error message I am getting. My SQL is dynamic so there is no specific error I want to log in table.

    Executed as Single statement. Failed [7547 : HY000] Target row updated by multiple source rows.
    Elapsed time = 00:00:00.171

    STATEMENT 1: Update Statement failed.

LEAVE A REPLY

Please enter your comment!
Please enter your name here