Re: URGENT !! Handling errors in Stored procedures
Originally posted by Jayathirtha
I want to handle errors in the stored procedure.
If there is a deadlock for some resource, then i would like to handle it in a separate way.
I assume you want to handle if the resource is locked:
cursor cur_upd is
select 'x' from table
where condition here
for update nowait;
fetch cur_upd into v_tmp;
when record_locked then
if cur_upd%isopen then
In stored procedures you use the BEGIN ... EXCEPTION structure...
EXCEPTION WHEN <named_exception> THEN
WHEN OTHERS THEN
The named_exception can be a pre-defined error such as NO_DATA_FOUND, or it can be a SQL Error Code (NO_DATA_FOUND = 100). The WHEN OTHERS section is the final "catch-all" error situation. When an error is found within the BEGIN ... END portion of your code, the specific error must be found to execute that error-handling routine. WHEN OTHERS is for all other error conditions. You can also create your own exception codes and use the RAISE syntax to force your code to "error out".