ON EXCEPTION routine not working
Hi,
I have an on exception routine defined to catch error -111 such as
follows:
create procedure my_proc ()
Foreach cursor for
select ...
into ...
from ...
where ...
on exception in (-111) set errnum
if errnum = -111 then
let return_status = 'NoRecordFound';
end if
let profname = (select aprofname from ptable where oid = poid);
end exception with resume
if return_status = 'NoRecordFound' then
let srvtype = 'No Profile definition found';
else
let srvtype = (select service from stable where sprofname =
profname);
end if
insert into temp_table values(...)
end foreach
This is not being accepted on execution. Can someone explain why?
Basically, what I'm trying to do is trap the 'no rows found' scenario
for the variable profname on the select. If now rows found then set
the return_status so that I can explictly set another variable that
should contain a value before insertion
into the temp table.
Regards,
Jeff
_________
On Mon, 05 May 2003 12:56:13 -0400, JeffC wrote:
Try moving the ON EXCEPTION clause to BEFORE the foreach loop.
Art S. Kagel
> Hi,
>
> I have an on exception routine defined to catch error -111 such as
> follows:
>
> create procedure my_proc ()>
> Foreach cursor for
> select ...
> into ...
> from ...
> where ...>
> on exception in (-111) set errnum
> if errnum = -111 then
> let return_status = 'NoRecordFound';
> end if
> let profname = (select aprofname from ptable where oid = poid);
> end exception with resume
>
>
> if return_status = 'NoRecordFound' then
> let srvtype = 'No Profile definition found';
> else
> let srvtype = (select service from stable where sprofname =
> profname);
> end if
>
> insert into temp_table values(...)>
> end foreach
>
>
> This is not being accepted on execution. Can someone explain why?
> Basically, what I'm trying to do is trap the 'no rows found' scenario
> for the variable profname on the select. If now rows found then set the
> return_status so that I can explictly set another variable that should
> contain a value before insertion into the temp table.
>
> Regards,
> Jeff
> _________