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?

Ramires

Answer 1

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 ...

Archeology

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

Answer 2

I think the part that's tripping you up is this:

If FETCH never executes successfully, the EXIT WHEN condition is never TRUE and the loop is never exited.

Somewhere in the past there must have been a code example which looked like this:

LOOP
  FETCH c1 INTO name;
  EXIT WHEN c1%NOTFOUND;
  -- Do stuff
END LOOP;

Given this chunk of code, then the statement rings true. If the fetch never executes (fails), then %NOTFOUND will be null. The EXIT WHEN condition will not evaluate to TRUE (null evaluates to false). Then, indeed, the loop will continue forever.

Fiend

Why would the fetch never execute in that situation though? – Ramires

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

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". – Ramires

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. – Fiend

Answer 3

This is a situation that is easily tested:

SET SERVEROUT ON;

DECLARE
  -- this cursor returns a single row
  CURSOR c1 IS
    SELECT 1 FROM dual WHERE rownum = 1;

  -- this cursor returns no rows
  CURSOR c2 IS
    SELECT 1 FROM dual WHERE 1=0;

  v1 number;
BEGIN
  OPEN c1;
  FETCH c1 INTO v1; -- this returns a record
  FETCH c1 INTO v1; -- this does not return a record
  IF c1%NOTFOUND THEN
    dbms_output.put_line('c1%NOTFOUND: TRUE');
  ELSIF c1%NOTFOUND IS NULL THEN
    dbms_output.put_line('c1%NOTFOUND: NULL');
  ELSE
    dbms_output.put_line('c1%NOTFOUND: FALSE');
  END IF;
  CLOSE c1;

  OPEN c2;
  FETCH c2 INTO v1; -- this does not return a record
  IF c2%NOTFOUND THEN
    dbms_output.put_line('c2%NOTFOUND: TRUE');
  ELSIF c2%NOTFOUND IS NULL THEN
    dbms_output.put_line('c2%NOTFOUND: NULL');
  ELSE
    dbms_output.put_line('c2%NOTFOUND: FALSE');
  END IF;
  CLOSE c2;
END;
/

The output of the script on Oracle APEX 4.1 is (I think APEX is running Oracle 11gR2, but you can easily run the script on any version):

c1%NOTFOUND: TRUE
c2%NOTFOUND: TRUE

Based on this test, %NOTFOUND will not be NULL after a fetch has been executed. This matches what the 10g and 11g documentation says in the initial description of the %NOTFOUND attribute. The note about the loop never exiting must be from an old version of the example. Since it's just a note, I'd says it's safe to trust the initial description and disregard the note.

Hicks