We have a Chinese version(http://scn.sap.com/community/chinese/hana/blog/2014/04/02/sap-hana-sqlscript-procedure%E4%B8%AD%E7%9A%84%E5%BC%82%E5%B8%B8%E5%A4%84%E7%90%86) of this document.

     For SQL procedure using SQLScript, SAP HANA provides corresponding methods of exception handling. There are three tools can be used: EXIT HANDLER, CONDITION, SIGNAL or RESIGANL.

     An example using EXIT HANLEDR shows as follows.


CREATETABLE MYTAB (I INTEGERPRIMARYKEY);
CREATE PROCEDURE MYPROC1
AS BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
INSERT INTO MYTAB VALUES (1);
INSERT INTO MYTAB VALUES (1); -- expected unique violation error: 301
END;

     When invoking this procedure, an exception of violating unique constraint will be thrown, for inserting the same content on primary key. Then the invocation well be suspended, and the subsequent operations will not be executed. After suspending the procedure, the action operations of EXIT HANDLER will be executed.

     Notice: We can use  “::SQL_ERROR_CODE”,”::SQL_ERROR_MESSAGE” to get the SQL ERROR CODE and related error message of the caught exception.

EXIT HANDLER

When declare an EXIT HANDLER in SQLScript Procedure, the syntax shows as below.


DECLARE EXIT HANDLER FOR <proc_condition_value_list> <proc_stmt>;
<proc_condition_value_list> ::= <condition_value> [ { <comma> <condition_value> }... ]
<condition_value> ::= <sql_error_code> | <condition_name> | SQLEXCEPTION
<sql_error_code> ::= SQL_ERROR_CODE <numeric_literal>

     We can declare an EXIT HANDLER to handle exceptions of the given SQL ERROR CODE or user defined condition in SQL procedures and provide the subsequent operations after the exception is thrown.

Notice

SAP HANA provides common SQL error code

http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/a78d3275191014b41bae7c4a46d835/content.htm

or checkSAP HANA SQL and System Views Reference”->1.10  SQL Error Codes

CONDITION

     You can declare a CONDITION in SQL procedure to define a condition which contains a SAP HANA error code or used-defined error code and name it. Then you can declare an EXIT HANDLER to handle exception of this condition, or use SIGNAL or RESIGNAL to throw an exception of a condition containing a user-defined error code.

     The syntax of CONDITION declaration


DECLARE <condition name> CONDITION [ FOR <sql_error_code> ];
<sql_error_code> ::= SQL_ERROR_CODE <numeric_literal>

SIGNAL/RESIGNAL

     SIGNAL or RESIGNAL can be used to throw exception in SQL procedure. But here exception only represents used-defined error, whose error code is between 10000 and 19999.

     The difference between SIGNAL and RESIGNAL is that 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. That is to say, RESIGNAL can be used to throw exception of the EXIT HANDLER catches to the caller. A user-defined exception can be catch by the corresponding EXIT HANDLER, or caught by the caller, same as an exception contains error code.

The syntax of using SINGAL or RESIGNAL


SIGNAL <signal_value> [<set_signal_information>] ;
RESIGNAL [<signal_value>] [<set_signal_information>];
<signal_value> ::= <condition_name> | <sql _error_code>
<set_signal_information> ::= SET <signal_information_item>
<signal_information_item> ::= MESSAGE_TEXT  <equals operator> <simple_value_specification>
<simple_value_specification> ::= <procedure_expression>

Examples

1.General exception handling


CREATETABLE MYTAB (I INTEGER PRIMARYKEY);
CREATE PROCEDURE MYPROC
AS BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
       SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
    INSERT INTO MYTAB VALUES (1);
    INSERT INTO MYTAB VALUES (1); -- expected unique violation error: 301
    -- will not be reached
END;
CALL MYPROC;

/wp-content/uploads/2014/06/1_482427.png

2.Error code exception handling


CREATETABLE MYTAB (I INTEGERPRIMARYKEY);
CREATE PROCEDURE MYPROC
AS BEGIN
    DECLARE myVar INT;
    DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 1299       SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
    SELECT I INTO myVar FROM MYTAB; --NO_DATA_FOUND exception
END;
CALL MYPROC;

/wp-content/uploads/2014/06/2_482428.png

The error message of SQL ERROR CODE 1299 is ”No data found”.

/wp-content/uploads/2014/06/3_482429.png

3.Conditional Exception Handling


CREATETABLE MYTAB (I INTEGERPRIMARYKEY);
CREATE PROCEDURE MYPROC
AS BEGIN
    DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 301;
    DECLARE EXIT HANDLER FOR MYCOND
        SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
    INSERT INTO MYTAB VALUES (1);
    INSERT INTO MYTAB VALUES (1); -- expected unique violation error: 301
    -- will not be reached
END;
CALL MYPROC;

/wp-content/uploads/2014/06/4_482430.png

4.Throw an exception using SIGNAL


CREATEPROCEDURE MYPROC
AS BEGIN
    DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;
    DECLARE EXIT HANDLER FOR MYCOND
        SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
    SIGNAL MYCOND SET MESSAGE_TEXT = 'my error';
    -- will not be reached
END;
CALL MYPROC;

/wp-content/uploads/2014/06/5_482431.png

5.Throw an exception using RESIGNAL


CREATEPROCEDURE MYPROC
AS BEGIN
    DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;
    DECLARE EXIT HANDLER FOR MYCOND RESIGNAL;
    SIGNAL MYCOND SET MESSAGE_TEXT = 'my error';
    -- will not be reached
END;
CALL MYPROC;

/wp-content/uploads/2014/06/6_482432.png

6.When using EXIT HANLDER, related tables can be used to store the exception information.


CREATETABLE ERROR_LOG
(ERROR_CODE VARCHAR(5),
 ERROR_MSG VARCHAR(200),
 TIME_STAMP TIMESTAMP);
CREATE PROCEDURE MYPROC
AS BEGIN
DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;
DECLARE EXIT HANDLER FOR MYCOND signal mycond set message_text = 'error';
 SIGNAL MYCOND;
-- will not be reached
END;
CREATE PROCEDURE MYPROC1
AS BEGIN
DECLARE EXIT HANDLER FOR sqlexception
INSERT INTO ERROR_LOG VALUES(::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE,CURRENT_TIMESTAMP);
CALL MYPROC;
END;   
CALL MYPROC1;

/wp-content/uploads/2014/06/7_482433.png

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Lars Breddemann

    Peeeleeeeaaaaaseeee…

    Not another one of those “I-copy-the-example-from-the-documentation-and-take-screenshots“-blogs.

    There’s more to it, isn’t it?

    You have a story to share, don’t you?

    Then by all means: tell us the story!

    What are you doin’ with the exception handling?

    How are you using it in your application? What do you like about it and what do you think is crap?

    Give us something from you. Tell us about your experiences and insights.

    Boring us to death is already perfectly done by the reference documentation 😉 (no offence docu colleagues, reference documentation should be complete, not necessarily exciting).

    Looking forward to your original blog posts that are to come!

    – Lars

    (1) 
    1. Peter Thawley

      Hi Lars …  I am wondering why the EXIT HANDLER model to catch exceptions is limited to SQL or SQLScript error codes (messages beginning with either ERR_SQL_* or ERR_SQLSCRIPT_*)?  I need to capture a “transaction” error (ERR_TX_*) for a DML operation specified with the NOWAIT option.  This returns error 146 (ERR_TX_LOCK_ACQUISITION_FAIL) and there seems to be no way to catch this in the procedure itself.

      Is there no notion of a SYSTEM VARIABLE holding the error code value for the last statement executed?  Seems like a huge shortcoming in the SQLScript language layer to handle errors appropriately.

      Appreciate your guidance here.

      Peter

      (0) 
      1. Lars Breddemann

        I’m not involved with the SAP HANA development, so as to ‘why’ I cannot answer that.

        To me, however, it makes sense to allow SQL/SQLScript and user-defined error conditions to be handled in the exception handling block – which is what it does right now.

        e.g. when I want to catch the fact that my NOWAIT lock attempt didn’t suceed (SQL error 146) I can do this:

        do BEGIN
        DECLARE EXIT HANDLER FOR sqlexception
        select ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE from dummy;
        
        lock table cusers in exclusive mode nowait;
        END;

        I locked a record in CUSERS in another session, so the lock table cannot get through and this is the message I get:

        SAP DBTech JDBC: [146]: Resource busy and NOWAIT specified:
        "DEVDUDE"."DO": line 5 col 1 (at pos 114): [146] (range 3): Resource busy and NOWAIT specified:  (lock table failed on vid=)

        Concerning the “SYSTEM” variables, something you really shouldn’t want to have in a concurrent/parallel environment, I believe HANA 2 got some development for that.

        BTW: where do you get the error names ERR_SQL_ and ERR_SQLSCRIPT_ from?

        (0) 
  2. Orel Stringa

    Hi,

    I have noticed a strange behavior with ROLLBACK when using exit handler and was wondering if you have faced the same.

    It looks like EXIT HANDLER does an implicit commit when applied in a stored procedure that has multiple DML operations. This makes exit handler a problem as it can implicitly commit despite the fact that the stored proc hits an exception.

    Here is an example:

    Without exit handler:

    CREATE TABLE MYZTAB0 (I INTEGER PRIMARY KEY);

    CREATE PROCEDURE MYZPROC0 AS BEGIN

    INSERT INTO MYZTAB0 VALUES (1);

    INSERT INTO MYZTAB0 VALUES (1); — expected unique violation error: 301

    END;

    CALL MYZPROC0;

    Could not execute ‘CALL MYZPROC0’ in 84 ms 865 µs .

    [301]: unique constraint violated:  [301] “E0228574”.”MYZPROC0″: line 4 col 2 (at pos 70): [301] (range 3) unique constraint violated exception: unique constraint violated: Table(MYZTAB0), Index(_SYS_TREE_RS_#52275459_#0_#P0)

    Neither the first, nor the second insert got committed. This is exactly the desired behavior.

    With exit handler:

    CREATE PROCEDURE MYZPROC AS BEGIN

    DECLARE EXIT HANDLER FOR SQLEXCEPTION

    SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

    INSERT INTO MYZTAB VALUES (1);

    INSERT INTO MYZTAB VALUES (1); — expected unique violation error: 301

    END;

    CALL MYZPROC;


    The output of the call statement shows error code 301 as well as error text “unique…”

    However a SELECT on MYZTAB tables shows that a record has been inserted.

    Why? The rollback should apply to all DML operations of the stored procedure not just the second insert.

    (1) 
  3. Pavan Kumar Nayakanti

    Hello, am seeing a below error happening due to overflow.

    Error number :70404 ERROR MESSAGE :SQL submitted to ODBC data source <HS2> resulted in error <[SAP AG][LIBODBCHDB SO][HDBODBC] General error;314 numeric overflow: [314] “XXXXX”.”SP_XXX_LOAD”: line 177 col 1 (at pos 10760): [314] (range 3) numeric overflow exception: numeric overflow: Decimal: Digits beyond Precision at function __typecast__()>. The SQL submitted is <CALL XXXXX.SP_XXXXX_LOAD(YES , 2016 , 99 , 01 , 300)>. error Context :|Session JOB_SP_XXXX_LOAD|Work flow Dependent_WF_1|Work flow WF_XXXX

    am trying to find out smart ways to identify the record which is causing the Insert statement to fail – like writing the record content to a flat file/log file.

    Please suggest a smart approach/code to accomplish this.

    Thanks.

    (0) 

Leave a Reply