Trapping Errors

Consider a procedure that is supposed to return a total number of stolen cars.

It should show all, based on Brand and Color as input parameters:

Call the procedure: are both parameters present?

• No: Error – missing parameter(s)
• Yes: Are both parameters the correct datatype?
  • Not: Error – mismatched type(s)
  • Yes: Do the Brand/Color values exist?
    • No: Misleading output: invalid Brand or Color value means no cars exist to be stolen
    • Yes: Misleading output: Brand and Color are independent valid values, but no car with that combination of values exists, so no cars exist to be stolen

Correct: Yes: PlateNumbers to the Stolen table are joined and this combination is the only one occurring among stolen cars.

• The two errors will result in an abort if left unaddressed.
• If fully executed, two misleading output states would both report a misleading total of zero stolen cars.
• Only the last case provides a meaningful total (including zero total).

In this example, the procedure should have several possible exit points:

• The two errors should lead to messages to the user, indicating an unsuccessful execution due to bad inputs, and showing the required parameters/types.

• The first misleading output, if joined, will report zero stolen cars. However, this is misleading because the calculation is done with nonexistent values of Brand and/or Color. This branch of the logic tree should exit without joining and inform the user that the supplied Brand and/or Color is not valid. A list of unique values may be returned to the user to show valid values.

• The second misleading output, if joined, will also report zero stolen cars. However, this is because no cars exist in the Brand/Color combination. Even though every value supplied is valid, it is not meaningful to indicate a total of zero stolen cars. This branch of the logic tree should exit without joining and inform the user no such cars exist.

• Only if a valid and existing combination of Brand/Color is supplied should PlateNumber values be joined against the Stolen table, and a COUNT(*) performed. Note that zero totals in this case are meaningful.

Syntax:

12
DECLARE <condition name> 
   CONDITION [ FOR SQL_ERROR_CODE <ERROR_CODE> ];

Examples:

12
DECLARE invalid_input;

12
DECLARE invalid_input;
   CONDITION FOR SQL_ERROR_CODE 10001;

12
DECLARE divide_by_zero;
   CONDITION FOR SQL_ERROR_CODE 304;

Note that user-defined error codes must be within the range of 10000 to 19999.

The CONDITION declaration may be assigned to a specific error code:

• System error codes act as a description of that system code.

• User-defined error codes are not tied to specific error codes.

When a CONDITION has an error code assigned to it, it may be returned to the user when the CONDITION is raised. This is useful, because it allows the returned :SQL_ERROR_CODE system variable value to indicate not just that a block of code has finished executing, but the exit point at which it finished.

AnyCONDITION may be referenced by an EXIT HANDLER, as shown in the examples:

1
DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 301;

123
DECLARE EXIT HANDLER FOR MYCOND  
       SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE
       FROM DUMMY;

1
DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;

123
DECLARE EXIT HANDLER FOR MYCOND  
       SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE
       FROM DUMMY; 

1
DECLARE MYCOND CONDITION;

123
DECLARE EXIT HANDLER FOR MYCOND  
       SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE
       FROM DUMMY; 

These examples have different behaviors:

• Example 1 returns the error code (304) and associated text.

• Examples 2 and 3 do not produce the error code and error message when the procedure exits.

  • In example 2, no error 10001 will ever be encountered.

  • In example 3, the EXIT HANDLER does not know which error to react to.

  • In examples 2 and 3, no error message text is supplied to be returned.

  • In example 3, no error number is supplied to be returned.

However, custom error text can be associated with the third example, via the SIGNAL or RESIGNAL commands.

Thinking back to the example of the stolen cars, it's good for the user to know what the outcome of the procedure represents, but it may be even more important for the SQLScript/procedure that called the stolen cars stored procedure to know.

Depending on the meaning of the output, the calling code might disregard the result, prompt the user to try different values, or call further procedures and pass the result. This can be accomplished by intentionally declaring a CONDITION or custom error code to be in effect, also by means of the SIGNAL or RESIGNAL commands.

Syntax:

12
SIGNAL (<USER_DEFINED_CONDITION> | SQL_ERROR_CODE <INT> )
   [SET MESSAGE_TEXT = '<MESSAGE_STRING>']

Examples:

1
SIGNAL SQL_ERROR_CODE 10001;

1
SIGNAL invalid_input;

12
SIGNAL invalid_input;
   SET MESSAGE_TEXT = 'Invalid input arguments';

The first example assigns a custom error code to the value of :SQL_ERROR_CODE (which must fall in the previously-discussed range of 10000 to 19999). In the second example, the execution is declared to be in the state of invalid_input, which would have been set by a DECLARE CONDITION statement (not shown). In both examples, there is no value of the associated system variable :SQL_ERROR_MESSAGE.

In the third example, a value is assigned to :SQL_ERROR_MESSAGE and may be returned.

You can raise exceptions in functions and with SQLScript.

123456789101112131415161718192021222324252627
CREATE FUNCTION Convert_Hours
(im_hours INTEGER, im_to VARCHAR(1)
RETURNS ex_result DEC(5,2)
AS
BEGIN

-- declare a condition called "UNKNOWN_UNIT" with custom error code value 10001
DECLARE UNKNOWN_UNIT CONDITION FOR SQL_ERROR_CODE 10001;

-- use an IF structure to set the value of ex_result to the appropriate value
-- by multiplying im_hours by the factor specified by im_to
-- in the event of an invalid im_to value, raise the exception UNKNOWN_UNIT
IF :im_to = 'm' 
    THEN ex_result := :im_hours * 60;

ELSEIF :im_to = 'd' 
    THEN ex_result := :im_hours / 24;

ELSEIF :im_to = 'h' 
    THEN ex_result := :im_hours;

ELSE 
    SIGNAL UNKNOWN_UNIT
    SET message_text = 'Target unit' || :im_to || 'not supported';
END IF;
   
END

In this example, the UNKNOWN_UNIT exception is defined as having error code 10001. If a non-valid value of: im_to is supplied, the UNKNOWN_UNIT exception is declared to be in effect (setting the current value of :SQL_ERROR_CODE to 10001), and the message_text string assigned to :SQL_ERROR_MESSAGE. The code and string are incorporated into the system-generated error that is returned to the user.

When combined with an EXIT HANDLER, the SIGNAL can control which portions of SQLScript are executed.

You can raise exceptions in procedures and with SQLScript.

1234567891011121314151617181920212223
-- create a proc with no parameters that does nothing but define exit conditions
CREATE PROCEDURE MYPROC 
AS 
BEGIN

-- declare the exit condition "MYCOND" for custom error code 10001
DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;

-- declare what actions to take if MYCOND is declared in effect:
-- return the value of SQL_ERROR_CODE and SQL_ERROR_MESSAGE
DECLARE EXIT HANDLER FOR MYCOND
        SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE 
            FROM DUMMY;

-- raise MYCOND and set the value of :SQL_ERROR_MESSAGE to the string 'my error'
-- because MYCOND is associated with an EXIT HANDLER, the proc will cease execution 
-- immediately after the actions specified in the definition of the EXIT HANDLER
SIGNAL MYCOND SET MESSAGE_TEXT = 'my error';

-- all following statements will be skipped as the proc exits
INSERT INTO MYTAB VALUES (1); 

END;

In this example, the MYCOND exception is defined as having error code 10001, and an EXIT HANDLER is declared. In the event of MYCOND being true, the values of :SQL_ERROR_CODE (10001) and :SQL_ERROR_MESSAGE (undefined) will be returned to the user via a SELECT.

The SIGNAL command then declares MYCOND to be in effect and supplies a message text so that :SQL_ERROR_MESSAGE is no longer undefined. When called, the stored procedure stops before reaching the INSERT command. Querying the MYTAB table will reveal it does not include the value of 1.

The RESIGNAL command is similar to SIGNAL, but is used exclusively in the EXIT HANDLER. As such, it does not declare a particular exception to be in effect. It can be used to pass along a condition name/error code number, or message text.

12345678910111213141516171819
-- create a proc that takes in an integer and returns a table consisting of
-- a single column named "1" with type integer
CREATE PROCEDURE MYPROC2
(IN in_var INTEGER, OUT outtab TABLE (1 INTEGER) )
AS

BEGIN

-- declare an EXIT HANDLER for any SQL error that may occur; the handler should
-- create a custom error message indicating the input parameter value led to this exception
DECLARE EXIT HANDLER FOR SQLEXCEPTION
    RESIGNAL SET MESSAGE_TEXT = 'for the input parameter in_var = ' || :in_var || ' an exception was raised ';

-- if any SQL error occurs, the EXIT HANDLER will populate :SQL_MESSAGE_TEXT and cease execution
-- otherwise, populate the value of the output table parameter

outtab = SELECT 1/:in_var as I 
             FROM DUMMY;
END;

Alternatively:

123
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  RESIGNAL SET MESSAGE_TEXT = 'for the input parameter in_ar = ' || :in_var || 
                              ' an exception was raised ' || ::SQL_ERROR_MESSAGE;

In the first example, an integer is supplied as input, and its reciprocal integer returned as output. If a zero value is supplied, a divide-by-zero error will be raised and caught by the EXIT HANDLER as a SQLEXCEPTION. In this case, the RESIGNAL command is used to place customized text in the error message – the string for the input parameter in_var = is concatenated with the value of: in_var and the string exception was raised.

In the second (partial) example, the RESIGNAL command uses the same custom text as in the first example, then further concatenates the original value of :SQL_ERROR_MESSAGE, which is preserved.

The EXIT HANDLER in SQLScript already offers a way to process exception conditions in a procedure or a function during execution. The CONTINUE HANDLER not only allows you to handle the error but also to continue with the execution after an exception has been thrown.  More specifically, SQLScript execution continues with the statement following the exception-throwing statement right after catching and handling the exception.

Please note the following in regards to the CONTINUE HANDLER statement:

• CONTINUE HANDLER is not supported in any procedure or function placed in a parallel execution block statement (that is, BEGIN PARALLEL EXECUTION ... END;) as it is not possible to determine the next statement (as all procedures and functions are executing in parallel).

• If there is an error in a conditional statement for an IF, a WHILE, or a FOR block, the whole block will be skipped after handling the error because the condition is no longer valid.

• The value of the variable remains as it was before the execution of the statement that returns an exception.

There are more than one thousand distinct errors in the latest release of SAP HANA (as of this writing, SAP HANA 2.0 SPS08) capable of being thrown by the SQL Engine Runtime. All of them have a unique number assigned by SAP along with a unique name. The number assigned for each distinct error ranges up 5737 (not all numbers are used). Each error name uses a prefix to indicate category; the prefixes include:

• ‘ERR_SQL_‘ (356 different errors)

• ‘ERR_TX_‘ (34 different errors)

• ‘ERR_SQLSCRIPT_‘ (161 different errors)

• ‘ERR_API_‘ (41 different errors).

All errors with prefix ‘ERR_SQL_‘, ‘ERR_SQLSCRIPT_‘, and (SPS04 at later) ‘ERR_TX_‘ can be caught by the application developer.

Using the code in the figure, Catching Specific Errors, we have (on line 22) the unique error number 131 which has the unique name ERR_TX_ROLLBACK_LOCK_TIMEOUT.