Can %NOTFOUND return null after a fetch?

Question

This question raised a very interesting point; there seems to be a contradiction in the Oracle documentation on whether it's possible for %NOTFOUND to be null after a fetch. Is it?

To quote from the 11g documentation

Note: In Example 6-16, if FETCH never fetches a row, then c1%NOTFOUND is always NULL and the loop is never exited. To prevent infinite looping, use this EXIT statement instead: EXIT WHEN c1%NOTFOUND OR (c1%NOTFOUND IS NULL);

The documentation seems to directly contradict itself as it also says the following, which implies that after a fetch %NOTFOUND cannot be null.

%NOTFOUND (the logical opposite of %FOUND) returns:
NULL after the explicit cursor is opened but before the first fetch
FALSE if the most recent fetch from the explicit cursor returned a row
TRUE otherwise

The 10g documentation has a similar warning, which isn't, necessarily, a direct contradiction as it warns that a fetch might not execute successfully in order for this behaviour to be exhibited.

Before the first fetch, %NOTFOUND evaluates to NULL. If FETCH never executes successfully, the EXIT WHEN condition is never TRUE and the loop is never exited. To be safe, you might want to use the following EXIT statement instead:

EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

In what situations might a fetch either "fail" or might %NOTFOUND return null after a fetch has been executed?

Solution

I can find a situation where a fetch can fail:

declare
  i integer;
  cursor c is
    select 1 / 0 from dual;
begin
  open c;

  begin
    fetch c
      into i;
  exception
    when others then
      dbms_output.put_line('ex');
  end;

  if c%notfound is null then
    dbms_output.put_line('null');
  elsif c%notfound then
    dbms_output.put_line('true');
  else
    dbms_output.put_line('false');
  end if;
  close c;

end;

But this only makes your question stronger since it will evaluate to null, neither in 10g nor in 11g ...

Comments

[Ben]

Why would the fetch never execute in that situation though?

[Ben]

+1 for being overly smart about it :-). The examples in the documentation didn't have nested anonymous blocks...

[Adam Hawkes]

There could be a problem with the query where it is valid syntactically but fails due to other factors.

[Ben]

The point of the question is what are those other factors :-)? I can't think of a single way in which the code will get past the fetch in order to evaluate the %notfound yet the fetch will not have "worked".

[Adam Hawkes]

I'm as guilty as the documentation when it comes to bad examples and wording. The fetch, even if there's an error, should result in the %notfound to be true or false. It is only null before the first fetch (as described earlier in the docs). So, if the FETCH never executes (successfully or otherwise) then the %notfound is null.