Best way to deal with “no data found” exceptions

[richard_hirsch]

Hi,

Does anyone have a best-practice to deal with “no data found” exceptions:

For example:

       declare vValue integer;
     select x into vValue from table A where ……;     /* no data returned with the "where clause */

     /* “no data found” Exception breaks code execution*/

using "select count(x) from table A where ……" is possible but not the ideal solution.

Thanks,

D.

[former_member184768]

Hi D,

how about using EXIT HANDLER:

create procedure pr_no_data()

as

v_val varchar(3);

begin

DECLARE EXIT HANDLER FOR SQLEXCEPTION

select B into v_val from my_tab where A = 99999; -- where condition which doesn't return data

insert into my_log values (:v_val);

end;

On the forum, you may also find information using nested blocks of BEGIN and END so that the control can be passed from the inner BEGIN.. END block with exception to the outer BEGIN..END block. Personally I am yet to try that, so cannot confirm that for sure.

Regards,

Ravi