I have two ways for catching NO_DATA_FOUND exception (the second way don't catch an exception properly…)
First way
create or replace package body pkg_mnt_departments is
procedure p_get_data(ls_cursor out sys_refcursor) is
begin
begin
open ls_cursor for
select field1, field2 from mytable
where 1 = 2;
exception
when others then
dbms_output.put_line('Exception');
end;
end p_get_data;
end pkg_mnt_departments;
Second way
procedure get_data(id in number,
l_cursor out sys_refcursor)
is
begin
if (condition1) then
open l_cursor for
select field1, field2
from mytable
where fieldid = id;
fetch l_cursor into v_field1, v_field2;
if(v_field1 is null) then
--Execute sentences when cursor is empty
end if;
end if;
end;
I would like what's of these ways is the correct. The first way doesn't work but the second do…But I would like if I'm doing the right thing.
PD: For some cases the second way didn't work…I executed the query outside of procedure and returned rows but when it was executed by procedure don't…I don't know if the fact of have indexed field(fieldid) has influenced.
Thanks for help me 🙂
UPDATE
I did some changes in my procedure:
procedure get_data(id in number,
l_cursor out sys_refcursor)
is
begin
if (condition1) then
open l_cursor for
select field1, field2
from mytable
where fieldid = id;
fetch l_cursor into v_field1, v_field2;
if(l_cursor%rowcount = 0) then
--Execute sentences when cursor is empty
end if;
end if;
end;
But, Neither works…By some strange reason cursor didn't return data…My final solution was leaving to use cursors…Results were returned at another way
Best Answer
The
NO_DATA_FOUND
exception is only raised when aSELECT ... INTO ...
clause return no rows. It won't be raised when you use an explicit cursor and aFETCH
statement.I think in general, it's a matter of style/preference as to whether you use implicit or explicit cursors. I'm not really a developer, so others may wish to comment here. But an implicity cursor is expected to fetch 0 or 1 row. If it fetches 0, then you get NO_DATA_FOUND. Explicit cursors are meant for 0 to many rows. So you generally will open the cursor inside a loop where you will FETCH into variables. In theis case, you are testing for the "End Of Fetch". There is a cusror variable that you can use to test for this - %NOTFOUND. This is used for exiting the loop, something like