HANA SQL- Exit Handler

light111Exception Handling in HANA SQL Script


In the recent last post  HANA SQL- Default NULL ,  we came across a situation where a run time exception occurred and by adding a specific addition DEFAULT NULL, there is no more runtime exception and the execution proceeds to the next statement.  

But there might be situation where if an exception occurred, we don’t want to continue but to handle the exception so we don’t receive dump and also inform the caller about the situation. In one of the last post  HANA SQL – Error Codes  where HANA DB engine assign a runtime code to the exception are shown.

In this post  let’s handle the SQL exception.

Below is a AMDP procedure which is a dump/run time exception if the select statement fails. 

1

Test program that calls the AMDP procedure.

2

Debug Details- One you try to execute the SELECT statement, the dump occurs.

3

We can see the AMDP exception and the SQL CODE- 1299 [ DATA NOT FOUND].

4

It is possible to do a TRY .. CATCH and ENDTRY in our report and call the AMDP method call in the TRY block, this is something still we can do and there would be no run time dump. But here the interest is inside the DB procedure how to handle the exception.

This can be done through the EXIT HANDLER addition in the DB procedure as highlighted below.

5

Let’s execute again and see the debug details- 

6

Now when the SQL EXCEPTION occurred the control moved to the EXIT HANDLER section. We can see the SQL_ERROR_CODE and SQL_ERROR_MESSAGE.

7

The EXIT HANDLER section executes and we can do something here like filling a return message table with proper message to know about the situation to the caller. 

8

One the EXIT HANDLER section finish its execution then it just returns from the method [ it doesn’t execute remaining part after the statement where the exception occurred].

9

While Doing EXIT HANDLER we can specify a specific SQL_ERROR_CODE or in general we can add- SQLEXCEPTION which can handle all SQL ERROR CODEs.

10

Debug detaails-

11

Additionally we can declare a suitable user defined condition for the SQL ERROR CODE and EXIT Handler handles that condition.

1


 

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s