CODEWITHSUNDEEP
oracle-databaseplsqlcursor
Cesar Miguel
Cesar Miguel

Reputation: 771

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

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

Upvotes: 0

Views: 12311

Answers (2)

Anshul Chauhan
Anshul Chauhan

Reputation: 21

I am creating a Procedure which have a cursor to retrieve data from SALES table. If Cursor - sales_cursor, does not get any data, 👇this is how you raise NO_DATA_FOUND.

    CREATE OR REPLACE PROCEDURE FETCH_SALES (O_ID IN NUMBER, L_TOTALROWS OUT
NUMBER)
AS
CURSOR C IS SELECT * FROM SALES WHERE ORDER_ID=O_ID;
REC SALES%ROWTYPE;
BEGIN
OPEN C;

LOOP

FETCH C INTO REC;

IF(C%ROWCOUNT = 0) THEN
RAISE NO_DATA_FOUND;
EXIT;
END IF;
IF C%NOTFOUND THEN
  
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE(REC.SALES_AMOUNT);
END LOOP;
SELECT COUNT(1) INTO L_TOTALROWS FROM SALES
WHERE SALES_DATE = rec.sales_date;
EXCEPTION
 WHEN no_data_found THEN
 dbms_output.put_line('No such Order!');
 WHEN too_many_rows THEN
 dbms_output.put_line('You got more than 1 row!');
 WHEN others THEN
 dbms_output.put_line('Error!');
END;

DECLARE
 TOTAL_ROWS NUMBER;
BEGIN
 FETCH_SALES (1267, TOTAL_ROWS);
 DBMS_OUTPUT.PUT_LINE ('Total Number of rows: ' || TOTAL_ROWS);
END;

Upvotes: 1

BobC
BobC

Reputation: 4416

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;

Upvotes: 2

Related Questions