Skip to Content

EXIT HANDLER FOR SQL EXCEPTION in SAP HANA in PROCEDURE

Hi friends,

Here I tried to capture the error message in a separate table.but i can't get my output.

this is my code

create procedure "KABIL_PRACTICE"."PROC_ADV_SALES_INVENTORY"
(
IN "P_ID" integer,
IN "P_NAME" nvarchar(35),
IN "QTY" integer, 
OUT Result "KABIL_PRACTICE"."TT_SALES_PRODUCT_INVENTORY")
		LANGUAGE SQLSCRIPT
		    SQL SECURITY INVOKER
		as begin
		declare PROD_ID integer;
		declare INV integer;
		declare EP decimal (34,2);
		declare SALES_AMOUNT Decimal(34,2);
select count(*) into PROD_ID from "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" where "P_ID" = :P_ID;
select "Stock" into INV from "KABIL_PRACTICE"."PRODUCT_MASTER_PROC"  where "P_ID" = :P_ID;
select "USP" into EP from "KABIL_PRACTICE"."PRODUCT_ITEM_PROC"  where "P_ID" = :P_ID;
 IF PROD_ID = 0 THEN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION
 BEGIN
 insert into "KABIL_PRACTICE"."ERROR_LOG" values (::SQL_ERROR_CODE,::SQL_ERROR_MESSAGE);
 select * from "KABIL_PRACTICE"."ERROR_LOG";
END;
	 ELSE IF INV >= :QTY THEN
		SALES_AMOUNT := (:QTY * :EP);
		insert into "KABIL_PRACTICE"."SALES_PROC" ("P_ID","P_NAME","QTY","Sales_AMount","Time_Of_Sale") values (:P_ID,:P_NAME,:QTY,:SALES_AMOUNT,CURRENT_TIMESTAMP);
		update "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" set "Stock" = "Stock"- :QTY where  "P_ID" = :P_ID;
		Result = select "S_ID","P_ID","P_NAME","QTY","Sales_AMount" from "KABIL_PRACTICE"."SALES_PROC";
  END IF;
 END IF;
end;

when i call this procedure using this code:

call  "KABIL_PRACTICE"."PROC_ADV_SALES_INVENTORY" (100,'PEN',3,?);

it throws an no data found error with code 1299.its correct in my product item table there is no P_ID value is 100...

I just want to capture that code in and insert into another table like "ERROR LOG" table.

the same code which is i'm using for EXIT HANDLER SQL EXCEPTION is perfectly working for the below procedure:

CODE:

		CREATE PROCEDURE "KABIL_PRACTICE"."EXCEPTION_EXAMPLE2" (
		        IN ip_id integer,
		        IN ip_name nvarchar(40),
		        OUT ex_message nvarchar(200) )
		    LANGUAGE SQLSCRIPT
		    SQL SECURITY INVOKER
		    AS
		BEGIN
		declare E integer;

		DECLARE EXIT HANDLER FOR SQLEXCEPTION
		BEGIN
		E := ::SQL_ERROR_CODE;
		insert into "KABIL_PRACTICE"."ERROR_LOG" values (:E,::SQL_ERROR_MESSAGE);
		  ex_message := 'SQL Exception occured. Error Code is: ' || ::SQL_ERROR_CODE || ' Error message is: ' || ::SQL_ERROR_MESSAGE;
		END;
		INSERT INTO "KABIL_PRACTICE"."ERROR_TEST" VALUES( :ip_id, :ip_name);
		ex_message := 'Product "' || :ip_id || '" inserted successfully'; 
		END;

But it won't work the above first procedure. I doubted in an IF condition statement and also the placement of this code is correct or not ???

DECLARE EXIT HANDLER FOR SQLEXCEPTION
		BEGIN
		insert into "KABIL_PRACTICE"."ERROR_LOG" values (::SQL_ERROR_CODE,::SQL_ERROR_MESSAGE);
		select * from "KABIL_PRACTICE"."ERROR_LOG";

Thank you.....

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Nov 03, 2017 at 10:46 AM

    You might want to execute the INSERT in an autonomous transaction, so that it does not get rolled back with the rest of your transaction.

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 28, 2017 at 10:16 AM
    -1

    The second select already causes the exception, that is before your exception handler declaration. The exit handler declaration is simply is too late in the code, the exception is therefore not handled and the program immediately terminates. In your second program the exit handler declaration is before the insert statement, therefore it works.

    Also, the exit handler should not be defined inside the if-block if you want it to be considered outside the if-block.

    If you just want to write the log, you do not need to re-raise the exception and then you will also not need an autonomous transaction.

    Add comment
    10|10000 characters needed characters exceeded