HANA Exception Handling in Stored Procedure for ALTER Statement

[rehanshaikh]

The exception is not being handled and I get the error message (in red at the bottom) when I call the stored procedure as below.

call "_SYS_BIC"."pkg1::SP_RESET_PASSWORD" ('TESTUSER', 'Test11', ?)

I don't know what I am doing wrong. It works fine with a SELECT statement but not with ALTER. Please help.

[rindia]

Hi Rehan,

As per your error message, you given the password as 'Kumar' which has a length of 5.

Try giving the password with minimum length of 8, exa,ple: Kumar123

Regards

Raj

[pfefferf]

To my knowledge it is not possible to handle exceptions raised by dynamic SQL in stored procedures.

In your case it would be possible to use procedure "SYS"."IS_VALID_PASSWORD" to check if a valid password is transferred, before the ALTER statement is executed.

Here is a little check within an anonymous block which shows the interface (a password is transferred and an error code and error message is returned; in case of no error an error code 0 is returned):

do (OUT code INT => ?, OUT reply nvarchar(100) => ?)
begin
  declare error_code INT;
  declare error_message nvarchar(128);

  call "SYS"."IS_VALID_PASSWORD" ('Hello111', :error_code, :error_message); 
  
  code := :error_code;
  reply := :error_message;
end;