Hi,

How do I bypass raising an exception by Oracle when it cannot find data for a SELECT statement? I need to continue processing after the SELECT even though it didn't fetch any rows. It is not going to the SQL%NOTFOUND check block after the SELECT statement, rather, it goes to the exception block and breaking out of the program.

I know that I could have a seperate exception handling block for the SELECT and keep the process continue with the outer block. Since I have multiple SELECT checks to do, is there any other way to accomplish this?

Thanks in advance for suggestions.

EXCEPTION
WHEN OTHERS THEN RETURN;
END;

Or you can define explicit cursors for the select statement and use
cursor_name%NOTFOUND. No exception will be raised when no data found in this way. Otherwise you have to use multiple begin-exception-end blocks for individual selects.

That will still exit from my procedure. I want to have a single procedure where I need to try multiple SELECT's one after another and I want to continue with the processing even when any of the SELECT returns no rows.

1 declare
2 v_timestamp varchar2(32);
3 begin
4 dbms_output.put_line('Before');
5 begin
6 select timestamp into v_timestamp from sysstats where 1 = 2;
7 EXCEPTION
8 WHEN no_data_found THEN null;
9 end;
10 dbms_output.put_line('After ');
11 begin
12 select timestamp into v_timestamp from sysstats where 1 = 2;
13 EXCEPTION
14 WHEN no_data_found THEN null;
15 end;
16 select to_char(sysdate,'YYYY-MON-DD:HH24:MI') into v_timestamp from dual;
17 dbms_output.put_line('Now is - ' || v_timestamp);
18 EXCEPTION
19 WHEN OTHERS THEN NULL;
20* END;
bcm@MWH.ARES.MWH.COM> /
Before
After
Now is - 2004-JAN-28:13:27

PL/SQL procedure successfully completed.

Thank you guys for your valuable suggestions. I tried both the methods and both are working. But I still wish I could check the SQLCODE for a not found case immedialtely after the SELECT without worrying about the exceptions like what I used to do in Informix 4GL.

difference between userdefined exception and raise_applivation_error in oracle