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?
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.
Originally posted by anacedent
WHEN OTHERS THEN RETURN;
2 v_timestamp varchar2(32);
6 select timestamp into v_timestamp from sysstats where 1 = 2;
8 WHEN no_data_found THEN null;
10 dbms_output.put_line('After ');
12 select timestamp into v_timestamp from sysstats where 1 = 2;
14 WHEN no_data_found THEN null;
16 select to_char(sysdate,'YYYY-MON-DD:HH24:MI') into v_timestamp from dual;
17 dbms_output.put_line('Now is - ' || v_timestamp);
19 WHEN OTHERS THEN NULL;
20* END; bcm@MWH.ARES.MWH.COM> /
Now is - 2004-JAN-28:13:27
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.