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
SIGNAL { condition_name | SQLSTATE [VALUE] SQLSTATE_code } [ SET condition_information_item = value ] ;
where:
- condition_name
- The name of a variable declared to identify a condition within an SQL stored procedure.
- 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.
- CONDITION_IDENTIFIER
- The condition name specified in a SIGNAL or RESIGNAL statement.
- CONDITION_NUMBER
- Takes values from 1 to 16, where 16 is the maximum number of conditions that can be stored in the diagnostics area.
- MESSAGE_LENGTH
- The length of MESSAGE_TEXT in characters.
- 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.
- 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.
- SUBCLASS_ORIGIN
- The identification of the naming authority that defined the subclass value of RETURNED_SQLSTATE.
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
- 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 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:
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:
Data type: CHARACTER(5) CHARACTER SET LATIN |
CLASS_ORIGIN | If SIGNAL specifies:
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
- condition_name, see DECLARE CONDITION.
- SQLSTATE codes and their meanings, see SQLSTATE Mappings.
- The Diagnostics Area, see The Diagnostics Area.
- Condition name is specified in a SIGNAL statement, it must be declared in the scope that applies to the SIGNAL statement, see Example: Using a Condition Name in a SIGNAL Statement.
- Condition name in a SIGNAL statement is equivalent to the usage of the SQLSTATE value to which the condition name corresponds, see Example: Using a Condition Name In a SIGNAL Statement and a Handler Declaration.
- Rules for the Diagnostics Area and condition handling, see RESIGNAL and BEGIN … END SIGNAL statement specifies a completion condition and no handler is defined within the compound statement to handle the condition, see BEGIN … END.
- More than one condition declaration is specified for the same condition name, see Example: The Condition Declaration and the SIGNAL Statement
- Data type of the value specified in the condition information item of a signal information specification Structure of the Diagnostics Area.