Oracle – Right way of catching NO_DATA_FOUND exception after opening a cursor in PL/SQL

cursororacleplsql

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 a SELECT ... INTO ... clause return no rows. It won't be raised when you use an explicit cursor and a FETCH 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

open c1
LOOP
  fetch c1 into var;
  exit when c1%notfound;
  -- do stuff
END LOOP;
close c1;