Skip to Content

SAP HANA: User-Defined Exception Handling

Hi Folks,

I was going through a basic requirement which i received today and thought of sharing the same with you.

Problem description:


If we have any select on a table which has ‘0’ records, instead of not displaying any data in the output we need to display “No Data Found” as the Output


/* Creating a Procedure for testing User-Defined Exception Handling */


CREATE PROCEDURE EXCEPTION_HANDLING AS BEGIN

DECLARE CountVar INT; /* Variable to count number of records in the table */
DECLARE CUSTOMCONDITION CONDITION FOR SQL_ERROR_CODE 10001;
/* Custom Error Code = 10001*/


/*User Defined exception handler */


DECLARE EXIT HANDLER FOR CUSTOMCONDITION

SELECT ::SQL_ERROR_CODE AS “Error Code”, ::SQL_ERROR_MESSAGE AS “Error Message” FROM DUMMY;


/* To check if the count = 0 to raise “no data found exception” */

SELECT COUNT (*) INTO CountVar FROM EXCEPTION_TEST;


IF CountVar  = 0

THEN
/* Signaling Custom error and setting the Custom Error Message */
SIGNAL CUSTOMCONDITION SET MESSAGE_TEXT = ‘No Data Found Exception’;


END IF;


END;



CALL EXCEPTION_HANDLING;


/*OUTPUT which shows the userdefined error code along with userdefined error message as shown below*/


Screen Shot 2014-01-15 at 10.09.07 PM.png



**Note:Please note that i have used the above example only to illustrate the “User Defined Exception” with the example.


The best way to check if the table is empty or not is by using “Exists” clause like the below statement:


SELECT 1 INTO CountVar FROM DUMMY WHERE EXISTS (SELECT 'X' FROM "EXCEPTION_TEST");


which if we use and if table is “Empty” the exception gets caught by SQLEXCEPTION with error code = 1299


Screen Shot 2014-01-15 at 10.13.48 PM.png


Do let me know your views and let me know any-other better alternate ways which you might have used.


Your’s

Krishna Tangudu πŸ™‚

9 Comments
You must be Logged on to comment or reply to a post.
  • Really nice idea Krishna.

    I am just a novice when it comess to HANA. There are two things I need some more clarity,

    1) I dont understand the use of DUMMY table here. When I do a SELECT * in my system it just returns one row

    2) Is 10001 a standard error code that is returned for an empty table.

    Please answer these if you can spare  the time.

    Thanks,

    Benedict

    • Hi Vasanth,

      Thanks for your feedback πŸ™‚

      1) Dummy is very similar to Dual table in oracle. It is helpful when you want to view the results of system functions

      ex: select current_timestamp from dummy


      2) 10001 is a custom error code the exception am dealing here is a user defined exception.

      We can start declaring customer error codes from 10001.

      On the other hand the system generated error code for “No data found” exception is 1299

      Regards,

      Krishna Tangudu

  • Hi Krishna,

    Good one. Thank you.

    Can you clear my doubt please?

    In this specific example…

    Can’t we show user defined message like below.


    IF CountVar  = 0

    THEN

    SELECT  ‘No Data Found Exception’ FROM DUMMY;

    END IF;

    If the above code works fine, then what is the use of user defined exception handling.

    Could you please elaborate more on this or give one more example, how specifically user defined exception handling helps us.

    Please let me know if my doubt is correct ?

    Thanks,

    Sree

  • Hi Krishna,

    I was looking for exception handling and I found your post.

    I didn’t get the concept in here. Can you help me on a real example which is I am trying to do?

    I have USERS table and I want to make registration on that table. If the user is exists it will show me an error message. If not exits it will show me a success message.

    Assume that the table is only including userid,username and password.

    How can I do such an example?

    Thanks.

  • HI:

    I would like to do the same Stored Procured in HANA as Oracle. Saving in a table, error code, error msg and trace, is it posible?

    Here below is my SP

     

    3 hours ago|10 Views

    Hi:

    I have this error catch procedure in Oracle using sys.DBMS_UTILITY.

    Here is the Stored Procedure:
    CREATE PROCEDURE ENTERPRISE.Record_Error (
    IN errorcode VARCHAR(30 ),
    IN errormessage VARCHAR(4000)
    )

    LANGUAGE SQLSCRIPT AS
    l_code INTEGER ;
    l_mesg VARCHAR(32767) ;

    BEGIN
    if (errorcode IS NULL) then
    l_code := SQL_ERROR_CODE;
    else
    l_code := errorcode ;
    end IF ;
    if (errorcode is NULL) then
    l_mesg := SQL_ERROR_MESSAGE;
    else
    l_mesg := errormessage;
    end IF ;

    INSERT INTO error_log (error_code
    , error_message
    , backtrace
    , callstack
    , created_on
    , created_by)
    VALUES (l_code
    , l_mesg
    , sys.DBMS_UTILITY.format_error_backtrace
    , sys.DBMS_UTILITY.format_call_stack
    , CURRENT_DATE
    , USER);
    END;

    Thank you