cancel
Showing results for 
Search instead for 
Did you mean: 

Handling exception in a for loop

Former Member
0 Kudos

Hi,

I want to write exception block inside a for loop like oracle as below.

for i in c1 loop

    begin

      insert into table values (i.id, i...);

    commit;

   exception when others then

     insert into erro_log values (i.id, i...);

   end;

end loop;


I want to handle same thing in hana. Is it possible in hana?


Does anybody knows how to handle exception in a for loop statement  or has an other solution to solve my problem described above?

Best regards,

Kishore



Accepted Solutions (0)

Answers (1)

Answers (1)

pfefferf
Active Contributor
0 Kudos

Hi Kshore,

are you asking regarding exception handling in SQLScript? If yes, then the answer is true. You can declare exit handlers for HANA standard SQL error codes and own defined error codes.

For detailed information please have a look to the documentation Exception Handling - SAP HANA SQLScript Reference - SAP Library. There are also some examples listed (Exception Handling Examples - SAP HANA SQLScript Reference - SAP Library).

Best Regards,

Florian

Former Member
0 Kudos

Hi Florian,,

I contains basic examples of exception handling. But when i am declaring exit handler in for loop block it is throwing exception like incorrect syntax error. For example i have 10 records in that 5 record is duplicate record. I want 5 record should go to error table and remaining records should load into target table.

Regards

Kishore.

pfefferf
Active Contributor
0 Kudos

To understand your issues it would be helpful if you could share your code.

But it is not possible to define the exit handler within the loop directly. I has to be defined at the beginning of e.g. a block. You could change your code, that within your loop you call a further procedure which does the handling of a single data set. Within that further procedure you can define your exit handler which inserts the data set in the log table with the help of an autonomous transaction in case your insert into the "main" table fails.

Best Regards,

Florian

Former Member
0 Kudos

Hi Florian,

My requirement is How to handle record level error logging? For example, if I have 10 records and 2 of them are failing due to some constraint violation in the target table, I would like the 8 records to go to target tables and the 2 records to be logged in an error table with the errors and primary key column value, instead of stopping the entire task with no record moving to target table.

I used below procedure, The records are inserting into target table until exception is raised, if exception raised it is logging to error table with error code and error message, but not the primary key column value and also remaining records are not inserting into target table.

create procedure exception_test()

language sqlscript

as

begin

DECLARE CURSOR C1 FOR select * from tab2;

DECLARE EXIT HANDLER FOR SQLEXCEPTION insert into err_test SELECT ::SQL_ERROR_CODE AS "ErrorCode", ::SQL_ERROR_MESSAGE AS "ErrorMessage",null FROM DUMMY;

delete from err_test;

for i as c1 do

insert into tab1 values(i.t1,i.t2);

end for;

end;

Note: When exception occurs, it is inserting into err_test table with error code and error message only. Third column is primary key column value, when I am using i.t1 it is throwing error. So I am passing null value here. But I want to capture error record with primary key column.

Could you please help me, how to handle this error codes in error table and also remaining records should insert into target table.

Regards,

Kishore.


pfefferf
Active Contributor
0 Kudos

Hello Kishore,

I think you did not understand what I tried to explain in my previous answer. Let me try again .

I would implement a procedure which does the insert + error handling for a single data record. E.g. here a simplified not complete procedure. You see that it gets a parameter which can be also be used within the exit handler section. So you can insert the values passed also to your error log table.


PROCEDURE "<schema>"."xxx.procedures::inserter"

  ( IN i_test nvarchar(256) )

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  as

begin

  declare exit handler for sqlexception

   begin

     select ::sql_error_code, ::sql_error_message, :i_test from dummy;

   end;

  -- do insert

end

A second procedure acts as caller of the first procedure for all data records you wann process. E.g. here an example which processes 10 records of the tables table.


PROCEDURE "<schema>"."xxx.procedures::call_inserter"

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  as

begin

  declare cursor c_data for select top 10 * from tables;

 

  for row as c_data do

    call "<schema>"."xxx.procedures::inserter"(row.table_name);

  end for;

end

So each data record will be handled separately. If e.g. the second data record insert fails, an entry in the log is written. But than the caller procedure will call the inserter procedure for the next data record.

But consider that the performance will not be the best in such a single data record processing.

Best Regards,

Florian

Former Member
0 Kudos

Thanks Florian for providing a detailed Explanation.

Regards,

Kishore.

Former Member
0 Kudos

Kishore,

Have you solved the issue? I have the similar question. I tried the approach Florian suggested.

For example, I have 20 tables,  I want to select a column from these tables, such as "select * from table_name" where column_name = 'A';

8 throw exception.  I want to the names of tables which throw exception saved in the error_log table, the others saved in another table.

the new question after using Florian's suggest is that how to pass the paramter (table_name) into "table_name" in the first procedure where ; select from table_name where column_name = 'A';

The table_name can be passed into error_Log table.

Thank you.