Reputation: 7279
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
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