Exception Handling in Teradata Teradata

At times, things may happen within stored procedures that are out of the ordinary. The status variables can be a major help in determining what happened. However, at times, the same error handling logic might be needed multiple times within the same procedure. When this is the case, a Handler routine may be written to "handle" the condition. This is an alternative to coding the same IF statements multiple times.

DECLARE HANDLER Statement

The DECLARE is also used to establish a Handler as well as a variable. A Handler is a section of logic that executes automatically based on error conditions that may occur during the execution of a stored procedure.

This is important because the procedure is executing without interaction from the user. It does not return until it is finished. So, rather than having an error occur and returning a code to the user to manually determine the next step, the proper action is coded as part of the procedure. It is programmed to "handle" the error condition and perform the necessary processing. Therefore, it is easier for the user and provides less opportunity for human errors and inconsistent results.

The syntax for DECLARE HANDLER:

The Handler type is one of these:

  • CONTINUE – Allows for the execution of handler logic within the procedure and then returns to the next sequential statement within the procedure after the one that caused the error condition
  • EXIT – Allows for the execution of handler logic within the procedure and then returns to the caller (exits procedure)

The Condition Value list is a comparison using one or more of the status variables.

The Handler action is the user logic written using SPL and DML necessary to handle the condition.

The next procedure contains two handler routines, the first one is a CONTINUE type and the second is an EXIT:

Every time an SQL error occurs that is a 41020 or 52100, a row is inserted into My_err_tbl with the error code and "Error Handled" and continues to execute. However, if any other error occurs, the SQLEXCEPTION picks it up and inserts the error code and "Unknown Error" into My_err_tbl. But, since it is an EXIT, the procedure ends. Granted, inserting a row is not much of a way to handle an error. Therefore, something more substantial is probably needed for your Handler routines. This is just a quick example of the technique involved in writing handler routines.


Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics