CODEWITHSUNDEEP
oracle-databaseif-statementcursor
Sharikov Vladislav
Sharikov Vladislav

Reputation: 7279

How to detect if fetch cursor returned no value?

I have this: 

declare
  toStoreA varchar2(10);
  toStoreB varchar2(10);
  toStoreC varchar2(10);

  cursor c1 is
    select a, b, c
    from table1
    where login = 'myLogin';
begin
  open  c1;
  fetch c1 into toStoreA,
                toStoreB,
                toStoreC
  close c1;

  if toStoreB = NULL then
    dbms_output.put_line('OK, we are in if, toStoreB is null');
  end if;

  dbms_output.put_line('toStoreA:' || toStoreA || '_');
  dbms_output.put_line('toStoreB:' || toStoreB || '_');
  dbms_output.put_line('toStoreC:' || toStoreC || '_');
end;

My target is to detect if fetch cursor returned no value.

If I will query my sql statement in sql window I will get this:

> select a, b, c from table1 where login = 'myLogin';
++++++++++++++++++++++++++++++++++++++++++++
+ some val +               +               +
++++++++++++++++++++++++++++++++++++++++++++

This is what I get in DBMS Output window: 

toStoreA:some val_
toStoreB:_ 
toStoreC:_

As you see, I don't get string OK, we are in if, toStoreB is null in DBMS Output. Why? Well, obvious if is not passed. The question is how to correctly check if fetch cursor returned null value (no value)?

I'd also tried if toStoreB = '' then but it didn't help.

Upvotes: 0

Views: 5250

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

You can't test for null with equality; null is never equal to (or not equal to) anything, including itself. And the empty string '' is indistinguishable from null, so you can't use equality tests for that either. See the explanation in the documentation. You can fix the immediate problem by using the is null operator instead:

  if toStoreB is NULL then

With that change you see:

anonymous block completed
OK, we are in if, toStoreB is null
toStoreA:some val_
toStoreB:_
toStoreC:_

I slightly misinterpreted the question initially, thinking you meant that you wanted to check if the fetch returned no rows, rather than that a specific column had no value; so the rest of this isn't directly relevant. If you're always expecting a single row then you can use select into ... instead of a named cursor; but with a cursor you can test for what was fetched more flexibly...

That only tells you that the fetch didn't find anything if b cannot be null; even if you think that's the case now it isn't something you should necessarily rely on, and isn't a generic solution.

You can check whether anything was fetched with the `%notfound' cursor attribute:

  open  c1;
    fetch c1 into toStoreA,
                  toStoreB,
                  toStoreC;
    if c1%notfound then
      dbms_output.put_line('No row was fetched');
    end if;
  close c1;

Note that it has to be checked before you close the cursor, usually straight after a fetch. It will error if you try to check it after the close. This is often used to break out of a loop that fetches, once all data has been retrieved.

So with both changes and a modified query that finds no data, you see:

anonymous block completed
No row was fetched
OK, we are in if, toStoreB is null
toStoreA:_
toStoreB:_
toStoreC:_

Upvotes: 4

Related Questions