CODEWITHSUNDEEP
oracle-databasestored-procedures
Nandita Rao
Nandita Rao

Reputation: 287

No data found error in oracle stored procedure

Please find below the stored procedure that I have written:

create or replace

procedure CursorProc_test_vp

(

  v_Cur IN OUT types.ref_cursor,

  count_var IN OUT number,

  var1 IN OUT varchar2,

  var2 IN OUT number,

  var3 IN  OUT number

)

as

begin

  select count(*) into count_var from vendor_backup;

  open v_Cur for 'select column1,column2 from BACKUP where column1 is not null and column2 is null';

    while count_var>0

    loop

      fetch v_Cur into var1,var2;

          SELECT ID into variable3 from BACKUP where column3 = var1;

          UPDATE BACKUP SET column2 = var3;

        count_var:=count_var-1;

      end loop;

  close v_cur;

end;

When I run the query with which I am opening the cursor I get 120 records. But when I run this stored procedure it says "No data found".

It has no compilation errors either. Am I missing something here?

Upvotes: 2

Views: 10596

Answers (2)

Alex Poole
Alex Poole

Reputation: 191265

Your count_var may be too high, i.e. more than 120, because it doesn't have the same filters as the cursor. If you have any rows in backup where column1 or column2 are null, you'll fetch too many times, and the fetches when you run out data will return null; and your inner select will then try to match on null, which will get an ORA-01403.

You may also not have a matching record in backup where column3 = var1. It isn't clear you have consistent data.

But assuming that part is valid, you can loop over the cursor without a separate count_var:

open v_Cur for select column1, column2 from backup where ... ;
loop
  fetch v_Cur into var1,var2;
  exit when v_Cur%NOTFOUND;

  SELECT ID into variable3 from BACKUP where column3 = var1;
  UPDATE BACKUP SET column2 = var3;
end loop;
close v_cur;

The loop will exit when the fetch runs out of results from the cursor.

Not sure why you have so many arguments to the procedure. It looks like you only need var3 to be passed in, and nothing really to be passed back out. v_Cur can just be declared as a cursor in the declare section, rather than as an argument.

Upvotes: 1

Panayiotis Savva
Panayiotis Savva

Reputation: 131

It's extremely difficult to try and put context behind the procedure without knowing what data you are passing, and what data is contained in the table.

The only other SQL that I would cause your NO_DATA_FOUND exception is:

"SELECT ID into variable3 from BACKUP where column3 = var1;"

Enclose this with a Begin Exception End Clause in your procedure and to try and Debug:

Begin
  SELECT ID into variable3 from BACKUP where column3 = var1;
Exception
When NO_DATA_FOUND Then
    DBMS_OUTPUT.PUT_LINE('ERROR: '||sqlerrm);
    Raise;  -- To ensure if this is the error, you will set get no_data_found raised... 
End;

Upvotes: 4

Related Questions