HANA SQL- Signal & Resignal

Signal and Resignal in SQL Script for Raising user defined exception

In the last post HANA SQL- Exit Handler which talks about the exit handler which is used to handle the raise SQL exception in the SQL script.

Upto now we have seen the exception is raised by the run time. It is quite possible to raise user defined exception as well.

The user defined exception can be raised by the SIGNAL and then this user defined exception can be handled with the EXIT HANDLER. And with the EXIT HANDLER again the same exception can be raised by the RESIGNAL.

Let’s get started:

User defined exceptions can be assigned a code from- 10000 to 19999.

Below AMDP procedure checks, if the input argument is 0 then it tries to raise an exception.

First declare a CONDITION and assign a SQL ERROR CODE and then at any point by using SIGNAL the user defined exception can be raised.

The test report that calls the AMDP method.

Debug Details and press F6 to execute SIGNAL statement and now the exception is raised and as it is not caught/handled so a run time DUMP appears.

ST22 details- the error code

Which line raised the exception-

When an exception is raised an error code and an error message is associated with that. We already have the user defined error code . Now with the SIGNAL statement raise the error message as shown below with SET MESSAGE_TEXT addition.

Now once the run time exception occurs during the execution we can find the exception message as well from ST22[long text].

Up to now we have raised the user defined exception and let’s handle this exception. System or user defined exception can be handled with EXIT HANDLER that we have seen in the last post HANA SQL- Exit Handler.

Let’s define the EXIT HANDLER section, so that we won’t get the dump as the raised exception is handled now.

Below here we have another AMDP procedure that checks the input and if it is 0 then it SIGNALs the exception and also handles the exception.

Out test report that calls the AMDP procedure.

Debug details- the GET_INFO procedure now calls the CHECK_INPUT procedure.

The CHECK_INPUT procedure now raises the exception with SIGNAL.

Now the control goes to the EXIT HANDLER.

EXIT Handler section is executing. So now we don’t receive any dump as the raised exception is handled properly.

It may be necessary that if we have handled the exception and then we can re-raise it by using the RESIGNAL addition and this RESIGNAL addition can only be used in the EXIT Handler.

In below, first we raised the exception with SIGNAL and then handled the same with EXIT HANDLER and then with addition RESIGNAL we again raised the same exception. Now if we will execute we will get a dump due to the RESIGNAL statement as it is used to inform the caller about the exception but it is not yet handled in the caller.