16.10 - SIGNAL - Teradata Database

Teradata Database SQL Stored Procedures and Embedded SQL

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
featnum
B035-1148-161K

Purpose

SIGNAL explicitly raises an exception, completion condition, not including Success, or user-defined condition in the Diagnostics Area.

Invocation

Executable.

Stored procedures only.

Syntax



condition_name
The name of a variable declared to identify a condition within an SQL stored procedure.
If condition_name specifies a condition that corresponds to an SQLSTATE value, the value of that SQLSTATE is assigned to RETURNED_SQLSTATE in the Condition Area.
SQLSTATE [VALUE] SQLSTATE_code
The value for an SQLSTATE to be assigned to RETURNED_SQLSTATE in the Condition Area.
value
A text or numeric value to be assigned to the specified condition information name.
condition_information_item
One of the following field names from the Condition Area of the Diagnostics Area as shown in the following table.
CLASS_ORIGIN
The identification of the naming authority that defined the class value of RETURNED_SQLSTATE.
The value must be ISO 9075 if the class value is defined in the ANSI/ISO SQL:2011 standard or Teradata if the class value is a Teradata class extension to the SQL:2011 standard.
Data type: VARCHAR(128) CHARACTER SET UNICODE
Default: null
CONDITION_IDENTIFIER
The condition name specified in a SIGNAL or RESIGNAL statement.
Data type: VARCHAR(128) CHARACTER SET UNICODE
Default: null
CONDITION_NUMBER
Takes values from 1 to 16, where 16 is the maximum number of conditions that can be stored in the diagnostics area.
Data type: INTEGER
Default: 0
MESSAGE_LEGTH
The length of MESSAGE_TEXT in characters.
Data type: INTEGER
Default: 0
MESSAGE_TEXT
The text of the Error or Warning message returned by the previous SQL statement execution or a message specified in a SIGNAL or RESIGNAL statement as signal information.
Data type: VARCHAR(128) CHARACTER SET UNICODE
Default: null
RETURNED_SQLSTATE
The SQLSTATE value returned by the previous SQL statement, the SQLSTATE value specified in a SIGNAL or RESIGNAL statement, or the SQLSTATE value associated with the condition name if a condition name is specified in a SIGNAL or RESIGNAL statement.
Data type: CHARACTER(5) CHARACTER SET LATIN
Default: null
SUBCLASS_ORIGIN
The identification of the naming authority that defined the subclass value of RETURNED_SQLSTATE.
The value must be ISO 9075 if the class value is defined in the ANSI/ISO SQL:2011 standard or Teradata if the class value is a Teradata subclass extension to the SQL:2011 standard.
Data type: VARCHAR(128) CHARACTER SET UNICODE
Default: null

Usage Notes

When a SIGNAL statement is executed, the Diagnostics Area is emptied and the Statement Area is filled in with the details of the SIGNAL statement and a Condition Area with condition number 1 is added in the Diagnostics Area corresponding to the SQLSTATE value or condition name specified in the SIGNAL statement. If signal information is specified in the SIGNAL statement, this added condition area is modified with the details given in the signal information.

Rules

The following rules apply to SIGNAL:
  • If a condition name is specified in a SIGNAL statement, it must be declared in the scope that applies to the SIGNAL statement. Otherwise, error SPL1079 is reported during stored procedure compilation.
  • The usage of a condition name in a SIGNAL statement is equivalent to the usage of the SQLSTATE value to which the condition name corresponds. This is true only if the condition name was declared with an SQLSTATE value. illustrates this rule.
  • If a SIGNAL statement specifies an exception or user-defined condition and no handler is defined within the compound statement to handle the condition, the rules for the Diagnostics Area and condition handling are same as listed for “RESIGNAL” and “BEGIN ... END.”
  • If a SIGNAL statement specifies a completion condition and no handler is defined within the compound statement to handle the condition, the rules for condition handling are the same as those for “BEGIN ... END.”
  • If a SIGNAL statement within a nonhandler compound statement specifies a user-defined condition, and no handler is defined to handle it in the compound statement or in any outer containing compound statement, Teradata Database returns a warning message during the compilation of the procedure. Then at runtime, the SIGNAL statement raises an exception with SQLCODE 7603 and SQLSTATE '45000'.
  • If more than one condition declaration is specified for the same condition name, the condition declaration that is most local to the scope of the compound statement containing the SIGNAL statement is used.
The following rules apply to signal_information:
  • The left hand side of a signal information specification can only stipulate one of the following Statement Area field names:
    • CLASS_ORIGIN
    • MESSAGE_TEXT
    • SUBCLASS_ORIGIN

      If a local variable, parameter, or a FOR loop alias/column is specified, Teradata Database aborts the request during compilation and returns an error to the requestor.

  • The left hand side of signal information cannot specify any of the following Statement Area field names:
    • CONDITION_IDENTIFIER
    • CONDITION_NUMBER
    • MESSAGE_LENGTH
    • RETURNED_SQLSTATE

      If you specify any of these, Teradata Database aborts the request during compilation and returns an error to the requestor.

  • The left hand side of signal information can only specify the following Statement Area fields if a condition name is specified in a SIGNAL statement, and the specified condition name is not associated with any SQLSTATE value:
    • CLASS_ORIGIN
    • SUBCLASS_ORIGIN

      Otherwise, Teradata Database aborts the request during compilation and returns an error to the requestor.

  • You cannot repeat any of the condition information item names in the signal information specification.

    Otherwise, Teradata Database aborts the request during compilation and returns an error to the requestor.

  • The data type of the value specified in the condition information item of a signal information specification must be compatible with the data type specified for each column in the Condition Area. Otherwise, Teradata Database aborts the request during compilation and returns an error to the requestor.
  • You cannot specify either ISO 9075 or Teradata for CLASS_ORIGIN or SUBCLASS_ORIGIN in the signal information variable.

    If you specify the right hand side of the signal information clause for CLASS_ORIGIN or SUBCLASS_ORIGIN as either ISO 9075 or Teradata, Teradata Database aborts the request during compilation and returns an error to the requestor.

    If the right hand side of the signal information clause for CLASS_ORIGIN or SUBCLASS_ORIGIN becomes either ISO 9075 or Teradata during runtime, Teradata Database aborts the request, returns an error to the requestor, and sets SQLCODE to 7609 and SQLSTATE to ‘T7609’.

Contents of the Statement Area

The following table specifies the contents of the Statement Area after the execution of a SIGNAL statement:

Field Name Contents
COMMAND_FUNCTION SIGNAL
COMMAND_FUNCTION_CODE 92
NUMBER 1
MORE N
ROW_COUNT 0
TRANSCATION_ACTIVE If no transaction is active, TRANSACTION_ACTIVE contains 0

If a transaction is active, TRANSACTION_ACTIVE contains 1

Contents of Condition Area 1

The following table specifies the contents of Condition Area 1 after the execution of a SIGNAL statement:

Field Name Contents
CLASS_ORIGIN If SIGNAL specifies:
  • An SQLSTATE value or the condition name associated with an SQLSTATE value, CLASS_ORIGIN contains ISO 9075 if the class value is defined in the ANSI/ISO SQL standard, or Teradata if the class value is a Teradata extension to the ANSI/ISO SQL standard.
  • A user-defined condition, CLASS_ORIGIN contains the value specified by the signal_information variable.
  • Neither of these, CLASS_ORIGIN contains a null.

Data type: VARCHAR(128) CHARACTER SET UNICODE

CLASS_ORIGIN If SIGNAL specifies an SQLSTATE value or the condition name associated with an SQLSTATE value, then CLASS_ORGIN is defined in ANSI/ISO SQL standard or Teradata if the class value is a Teradata extension to the ANSI/ISO SQL standard.

If SIGNAL specifies a user-defined condition, then CLASS_ORIGIN contains a value specified by the signal_information variable.

If SIGNAL specifies neither of these, then CLASS_ORIGIN contains a null.

Data type: VARCHAR(128) CHARACTER SET UNICODE

CONDITION_IDENTIFIER The condition name specified in the SIGNAL statement.

If SIGNAL does not specify a condition name, then this field is set null.

Data type: VARCHAR(128) CHARACTER SET UNICODE

CONDITION_NUMBER 1

Data type: INTEGER

MESSAGE_TEXT The value specified for MESSAGE_TEXT by the signal_information variable in the SIGNAL statement.

If SIGNAL does not specify a message text value, then this field is set null.

Data type: VARCHAR(128) CHARACTER SET UNICODE

MESSAGE_LENGTH The length of the MESSAGE_TEXT in characters.

If SIGNAL does not specify a message text value, then this field is set to 0.

Data type: INTEGER

RETURNED_SQLSTATE One of the following:
  • The SQLSTATE value associated with the condition specified in the SIGNAL statement.
  • The SQLSTATE value specified in the SIGNAL statement.
  • Null.

    The value is NULL if a condition name is specified in the SIGNAL statement and it is not associated with any SQLSTATE value.

Data type: CHARACTER(5) CHARACTER SET LATIN

CLASS_ORIGIN If SIGNAL specifies:
  • An SQLSTATE value or the condition name associated with an SQLSTATE value, CLASS_ORIGIN contains ISO 9075 if the class value is defined in the ANSI/ISO SQL standard, or Teradata if the class value is a Teradata extension to the ANSI/ISO SQL standard.
  • A user-defined condition, CLASS_ORIGIN contains the value specified by the signal_information variable.
  • Neither of these, CLASS_ORIGIN contains a null.

Data type: VARCHAR(128) CHARACTER SET UNICODE

SUBCLASS_ORIGIN If SIGNAL specifies an SQLSTATE value or the condition name associated with an SQLSTATE value, then SBUCLASS_ORIGIN contains ISO 9075 if the class value is defined in the ANSI/ISO SQL standard or Teradata if the class value is a Teradata extension to the ANSI/ISO SQL standard.

If SIGNAL specifies a user-defined condition, then SUB_CLASS contains the value specified by the signal_information variable.

IF SIGNAL specifies neither of these, then SUB_CLASS contains a null.

Data type: VARCHAR(128) CHARACTER ST UNICODE

Example: Using a Condition Name In a SIGNAL Statement and a Handler Declaration

The following example illustrates the usage of a condition name in a SIGNAL statement and a handler declaration defined for the SQLSTATE value associated with the condition name.

During stored procedure execution via Req1, the value of InParam2 is zero and the SIGNAL statement is executed.

The SIGNAL statement invokes the EXIT handler defined to handle SQLSTATE '22012'. Note that condition name divide_by_zero is associated with SQLSTATE '22012'.

Though the SIGNAL statement uses divide_by_zero and the handler is defined to handle the SQLSTATE value associated with divide_by_zero, the handler for SQLSTATE '22012' is invoked.

After successfully executing the handler action statement, control exits compound statement cs1 and the stored procedure terminates.

     CREATE PROCEDURE signalsp3 (IN  InParam1 INTEGER,
                                 IN  InParam2 INTEGER,
                                 OUT OParam3  INTEGER)
      cs1:BEGIN
          DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
          DECLARE EXIT HANDLER FOR SQLSTATE '22012'
              SET OParam3 = 0;
          IF (InParam2 = 0) THEN
              SIGNAL divide_by_zero;
          ELSE
              SET OParam3 = InParam1 + InParam2;
              ...
          END IF;
          ...
      END cs1;
     BTEQ> CALL signalsp3(10, 0, OParam3);

Example: Using a Condition Name in a SIGNAL Statement

The following example illustrates the usage of a condition name in a SIGNAL statement.

During stored procedure execution via Req1, the value of InParam2 is zero and the SIGNAL statement is executed.

The SIGNAL statement looks for a handler defined for divide_by_zero or its associated SQLSTATE '22012'.

Because there is an EXIT handler defined to handle divide_by_zero, it is invoked.

After successfully executing the handler action statement, control exits compound statement cs1 and the stored procedure terminates.

     CREATE PROCEDURE signalsp4 (IN  InParam1 INTEGER,
                                 IN  InParam2 INTEGER,
                                 OUT OParam3  INTEGER)
     cs1:BEGIN
         DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
         DECLARE EXIT HANDLER FOR divide_by_zero
             SET OParam3 = 0;
         IF (InParam2 = 0) THEN
             SIGNAL divide_by_zero;
         ELSE
             SET OParam3 = InParam1 + InParam2;
             ...
         END IF;
         ...
     END cs1;
     BTEQ> CALL signalsp4(10, 0, OParam3);

Example: The CONTINUE handler and Exceptions Raised by the SIGNAL Statement

In the following example, the CONTINUE handler defined in the containing outer compound statement handles the exception raised by the SIGNAL statement.

During the stored procedure execution, the SIGNAL statement signals divide_by_zero.

Because there is no handler defined to handle divide_by_zero in cs2, the SQLSTATE value associated with divide_by_zero is propagated to outer compound statement cs1 and is handled by the CONTINUE handler in cs1.

After the successful completion of the handler action statement, control returns to the line following SIGNAL divide_by_zero.

     CREATE PROCEDURE signalsp5 (IN  InParam1 INTEGER,
                                 IN  InParam2 INTEGER,
                                 OUT OParam3  INTEGER)
     cs1: BEGIN
          DECLARE CONTINUE HANDLER FOR SQLSTATE '22012'
              SET OParam3=1;
          DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012'
          cs2: BEGIN
              IF (InParam2 = 0) THEN
                  SIGNAL divide_by_zero;
                  …
              ELSE
                  SET OParam3 = InParam1 + InParam2;
                  …
              END IF;
              …
          END cs2;
          …
     END cs1;
     BTEQ> CALL signalsp5(10, 0, OParam3);

Example: Using Signal Information In a SIGNAL Statement

The following example illustrates the use of signal information in a SIGNAL statement.

During stored procedure execution, the SIGNAL statement sets MESSAGE_TEXT as 'balance is too low' and CLASS_ORIGIN as 'Stored Procedure'.

MESSAGE_LENGTH is implicitly set to 19.

The EXIT handler handles the condition.

The GET DIAGNOSTICS statement retrieves the MESSAGE_TEXT and CLASS_ORIGIN from the first condition area and assigns them to the output parameters, Message and Class.

After successful execution of the EXIT handler action statements, control exits compound statement cs1 and the stored procedure terminates.

     CREATE PROCEDURE setsignalsp1 (INOUT acno    INTEGER,
                                    INOUT amt     FLOAT,
                                    OUT   Message VARCHAR(50),
                                    OUT   Class   VARCHAR(50))
     cs1: BEGIN
          DECLARE balance_too_low CONDITION;
          DECLARE count INTEGER DEFAULT 0;
          DECLARE bal_amt, balance FLOAT;
          DECLARE EXIT HANDLER FOR balance_too_low
          BEGIN
              GET DIAGNOSTICS EXCEPTION 1
                 Message = MESSAGE_TEXT, Class = CLASS_ORIGIN;
              SET count = count + 1;
              INSERT INTO errortbl VALUES (acno, count, User,
                          current_timestamp, 'Balance too low for the
                                              account');
          END;
          SELECT balamt INTO balance
          FROM Deposit
          WHERE accountno = acno;
          SET bal_amt = balance - amt;
          IF  (bal_amt < 1000) THEN
               SIGNAL balance_too_low
                      SET MESSAGE_TEXT = 'Balance is too low',
                          CLASS_ORIGIN = 'Stored Procedure';
          ELSE
              UPDATE Deposit
              SET balance = bal_amt
              WHERE accountno = acno;
          END IF;
     END cs1;

Example: The Condition Declaration and the SIGNAL Statement

In the following example, the condition declaration that is more local to the scope of the compound statement containing the SIGNAL statement gets used.

During stored procedure execution, the value of InParam2 is zero and the SIGNAL statement in compound statement cs2 raises a user-defined condition divide_by_zero.

Because there is a handler for this condition in compound statement cs2, it is invoked. Even though compound statement cs1 has a handler defined to handle divide_by_zero, it is not invoked because the condition declaration is more local to the scope of the compound statement containing the SIGNAL statement.

Stored procedure execution continues after executing the handler action statement.

     CREATE PROCEDURE signalsp7 (IN  InParam1 INTEGER,
                                 IN  InParam2 INTEGER,
                                 OUT OParam3  INTEGER)
     cs1: BEGIN
          DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
          DECLARE EXIT HANDLER FOR divide_by_zero
              SET OParam3 = 0;
          cs2: BEGIN
              DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
              DECLARE EXIT HANDLER FOR divide_by_zero
                  SET OParam3 = 10;
              IF (InParam2 = 0) THEN
                  SIGNAL divide_by_zero;
              ELSE
                  SET OParam3 = InParam1 + InParam2;
                  …
              END IF;
              …
          END cs2;
          …
     END cs1;
     BTEQ> CALL signalsp7 (10, 0, OParam3);

Related Topics

For more information about: