Passes the signaled SQLSTATE status parameter back to the
application or SQL interface and terminates the current routine
and all calling routines.
1 – Environment
You can use the SIGNAL statement in a compound statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
SIGNAL -+-> value-expr ---------------------+-----+
+-> SQLSTATE VALUE 'string-literal'-+ |
+-----------------------<-----------------------+
++----------------------------+------------------>
+-> ( --> signal-arg --> ) --+
3 – Arguments
3.1 – signal-arg
Specifies a value expression. The specified value is converted to
a CHARACTER(80) CHARACTER SET UNSPECIFIED string and returned
as a secondary message to the client application. If the
value expression converts to a character string longer than 80
characters, it is truncated.
You can use the sql_get_error_text routine to extract the signal-
arg text in an application.
3.2 – string-literal
A quoted string literal which represents the SQLSTATE value.
3.3 – value_expr
Expects a character value expression which is used as the
SQLSTATE status parameter. Any provided value expression is
converted to a CHAR(5) value which is passed to SIGNAL.
See Value Expressions for more information on value expressions.
See SQLCA for more information about SQLSTATE.
4 – Examples
Example 1: Using the SIGNAL and RETURN statements, multiline
comments, and stored functions
The example uses a table, NEXT_KEY_TABLE, to maintain a list
of key names and their current values. In this example, only
a single key is created with the name EMPLOYEE_ID. Each time
the function is called, it fetches the value from the NEXT_KEY_
TABLE and returns the next value. If the named key is not found,
an error is returned (SQLSTATE 22023 is defined as "invalid
parameter value").
SQL> CREATE DOMAIN key_name
cont> CHAR(31)
cont> CHECK (VALUE IS NOT NULL)
cont> NOT DEFERRABLE;
SQL> --
SQL> CREATE TABLE next_key_table (
cont> next_key_val INTEGER NOT NULL,
cont> next_key_name key_name UNIQUE);
SQL> --
SQL> INSERT INTO next_key_table (next_key_name, next_key_val)
cont> VALUES ('EMPLOYEE_ID', 0);
1 row inserted
SQL> --
SQL> CREATE MODULE tools
cont> LANGUAGE SQL
cont> FUNCTION next_key (IN :key_name key_name)
cont> RETURNS INTEGER
cont> COMMENT IS 'This routine fetches the next value of the'/
cont> 'specified entry in the sequence table. The'/
cont> 'passed name is converted to uppercase before'/
cont> 'retrieval (see the DEFAULT clause for compound'/
cont> 'statements). The UPDATE ... RETURNING statement'/
cont> 'is used to fetch the new value after the update.'/
cont> 'If no entry exists, then an error is returned.';
cont> BEGIN
cont> DECLARE :rc, :new_val INTEGER DEFAULT 0;
cont> DECLARE :key_name_upper key_name DEFAULT UPPER(:key_name);
cont> DECLARE :invalid_parameter CONSTANT CHAR(5) = '22023';
cont> --
cont> UPDATE next_key_table
cont> SET next_key_val = next_key_val + 1
cont> WHERE next_key_name = :key_name_upper
cont> RETURNING next_key_val
cont> INTO :new_val;
cont> --
cont> GET DIAGNOSTICS :rc = ROW_COUNT;
cont> TRACE 'NEXT_KEY is ', COALESCE(:new_val, 'NULL'), ', RC is ', :rc;
cont> --
cont> IF :rc = 0 THEN
cont> TRACE 'No entry exists for KEY_NAME: ', :key_name_upper;
cont> SIGNAL :invalid_parameter;
cont> ELSE
cont> TRACE 'Returning new value for ', :key_name_upper, :new_val;
cont> RETURN :new_val;
cont> END IF;
cont> --
cont> END;
cont> END MODULE;
SQL> --
SQL> CREATE TABLE employee (
cont> employee_id INTEGER,
cont> last_name CHAR(20),
cont> birthday DATE);
SQL> --
SQL> -- Turn on the TRACE flag so we can see the function working.
SQL> --
SQL> SET FLAGS 'TRACE';
SQL> --
SQL> INSERT INTO employee (employee_id, last_name, birthday)
cont> VALUES (next_key('EMPLOYEE_ID'), 'Smith', DATE'1970-1-1');
~Xt: NEXT_KEY is 1 , RC is 1
~Xt: Returning new value for EMPLOYEE_ID 1
1 row inserted
SQL> --
SQL> INSERT INTO employee (employee_id, last_name, birthday)
cont> VALUES (next_key('EMPLOYEE_ID'), 'Lee', DATE'1971-1-1');
~Xt: NEXT_KEY is 2 , RC is 1
~Xt: Returning new value for EMPLOYEE_ID 2
1 row inserted
SQL> --
SQL> INSERT INTO employee (employee_id, last_name, birthday)
cont> VALUES (next_key('EMPLOYEE_ID'), 'Zonder', DATE'1972-1-1');
~Xt: NEXT_KEY is 3 , RC is 1
~Xt: Returning new value for EMPLOYEE_ID 3
1 row inserted
SQL> --
SQL> SELECT * FROM employee ORDER BY EMPLOYEE_ID;
EMPLOYEE_ID LAST_NAME BIRTHDAY
1 Smith 1970-01-01
2 Lee 1971-01-01
3 Zonder 1972-01-01
3 rows selected
SQL> --
SQL> -- Show the error if the unknown key_name is passed.
SQL> --
SQL> INSERT INTO employee (employee_id, last_name, birthday)
cont> VALUES (next_key('EMPLOYEEID'), 'Zonder', DATE'1972-1-1');
~Xt: NEXT_KEY is 0 , RC is 0
~Xt: No entry exists for KEY_NAME: EMPLOYEEID
%RDB-E-SIGNAL_SQLSTATE, routine "NEXT_KEY" signaled SQLSTATE "22023"
Example 2: Specifying a Secondary Error
SQL> BEGIN
SQL> SIGNAL SQLSTATE 'RR000' (' Compound Statement Failed');
cont> END;
%RDB-E-SIGNAL_SQLSTATE, routine "(unnamed)" signaled SQLSTATE "RR000"
-RDB-I-TEXT, Compound Statement Failed