How to Use RESIGNAL to Throw Exception

In the previous article How to Use SIGNAL to Throw Exception, we learnt how to implement SIGNAL in exception handling.
In this example we will learn – How to use RESIGNAL in exception.

Introduction:

The RESIGNAL statement raises an exception on the action statement in exception handler. If error code is not specified, RESIGNAL will throw the caught exception.

Difference between SIGNAL and RESIGNAL:
When using SIGNAL, you must point out the SQL ERROR CODE or CONDITION of the exception. But RESIGNAL can be used in the action part of an EXIT HANDLER all alone.
In this example we will use RESIGNAL to throw exception.

Create Table:

CREATE COLUMN TABLE <SCHEMA_NAME>.TABLE1 (
       ID INTEGER PRIMARY KEY,
       NAME VARCHAR(10)
);

Note: If you have already created this table in the previous example, no need to create it again.

Create procedure:

Copy and paste the below script to create the procedure.
--REPLACE <SCHEMA_NAME> WITH YOUR SCHEMA NAME
CREATE PROCEDURE "<SCHEMA_NAME>"."EXCEPTION_EXAMPLE_WITH_RESIGNAL" (
        IN ip_id integer,
        IN ip_name nvarchar(40),
        OUT ex_message nvarchar(200) )
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER
    AS
BEGIN
/*****************************
    Write your procedure logic
 *****************************/
DECLARE empty_name CONDITION FOR SQL_ERROR_CODE 10001;
DECLARE EXIT HANDLER FOR empty_name RESIGNAL;
IF :ip_name = '' THEN
    SIGNAL empty_name SET MESSAGE_TEXT = 'name cannot be empty';
END if;
INSERT into "<SCHEMA_NAME>"."TABLE1"
          values( :ip_id, :ip_name);
ex_message := 'Record inserted successfully';
END;

Call procedure:

Call the procedure using below statement.
CALL <SCHEMA_NAME>."EXCEPTION_EXAMPLE_WITH_SIGNAL"(10, '', ?);

This will RESIGNAL the exception to caller.

Click here to go to Next Chapter. 4.12 Cursor