Explicit Exception creation using SIGNAL, DESIGNAL and handling in HANA procedures.

In last session I have discussed about the normal exception handing using EXIT HANDLER .

In today’s session I will discuss about the explicit creation of exception handling using Signal and Condition.

Using our HANA procedure we will check for valid Email ID. If email id is not valid we will signal an exception using condition.

1. Create the below procedures :

Crated a procedure in which first define the signal as condition and declare a exception handler using signal and the validate the email ID –

CREATE PROCEDURE "SE_DEV"."DEMO_WITH_SIGNAL_EXCEPTION" (
        IN emai_id nvarchar(50),
        OUT display_message nvarchar(300) )
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER /* you can set it as DEFINER */
    AS
BEGIN
/* Declaration of Exit Handler */

DECLARE SIG_HANDLER CONDITION FOR SQL_ERROR_CODE 12001;
DECLARE EXIT HANDLER FOR SIG_HANDLER
BEGIN
  display_message := 'Error Code ' || ::SQL_ERROR_CODE || ' ' || ::SQL_ERROR_MESSAGE;
END;

/* Declaration End */

IF :emai_id <> '' AND :emai_id NOT LIKE '_%@__%.__%' THEN

    SIGNAL SIG_HANDLER SET MESSAGE_TEXT = 'Not an Email id';
END if;

display_message := 'Vaild Email Id';

END;

2. Call the procedure using a Valid email ID –

CALL “SE_DEV”.”DEMO_WITH_SIGNAL_EXCEPTION”(‘pallab.demo@gmail.com’, ?);

3.  Call the procedure using a Invalid email ID  throw the explicit exception and produce the following output –

Calling code – CALL “SE_DEV”.”DEMO_WITH_SIGNAL_EXCEPTION”(‘demo123gmailcom’, ?);

Result –