what is the default value of %NOTFOUND in cusor attribute?

Question

I am studying PL/SQL cursors. I have a problem with a cursor attribute. What a default value for %FOUND, %NOTFOUND in implicit and explicit cursors?

I am going through the PDF I found this sentence

LOOP
FETCH c1 INTO my_ename, my_sal, my_hiredate;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;

Before the first fetch, %NOTFOUND evaluates to NULL. So, if FETCH never executes successfully, the loop is never exited. That is because the EXIT WHEN statement executes only if its WHEN condition is true. To be safe, use the following EXIT statement instead:

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

If a cursor is not open, referencing it with %NOTFOUND raises INVALID_CURSOR.

Sequestered

Answer 1

In answer to your question there is no "default" value. %NOTFOUND is a "variable" that is instantiated upon the opening of a cursor. The value at that point is null; I guess you could call this the default if you wanted. After the first fetch of a cursor the value will either be true of false depending on whether any rows were returned. After the cursor is closed the "variable" no longer exists.

To quote from the 11gr2 documentation:

%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 useful table that demonstrates this

+----------------+--------+--------------+
|     stage      |  when  | return value |
+----------------+--------+--------------+
| open           | before | exception    |
|                | after  | null         |
| first fetch    | before | null         |
|                | after  | false        |
| next fetch(es) | before | false        |
|                | after  | false        |
| last fetch     | before | false        |
|                | after  | true         |
| close          | before | true         |
|                | after  | exception    |
+----------------+--------+--------------+

However, there does appear to be a direct contradiction in the Oracle documentation. The 11g documentation also has something similar to what you've described. The wording of which seems to directly contradict the above assertion

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 10g documentation is more explicit and slightly differently worded. This isn't a direct contradiction

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 general I would say that it's wise to trust the documentation and write EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL.

I don't know in what situations a fetch would not execute successfully; I've asked the question. If the fetch never returns a value due to locking etc then you'll never reach the part of the code to determine whether it failed or not.

René Nyffenegger has some code on his blog that is a good explanation of how %NOTFOUND works.

Tanagra