Re: PL/SQL and cursors. Any specialist?

Stephane Chiche asks about cursors which return no rows:  

> ...but what happens if I reference a field from
> the cursor, such as cur_CONSTANTS%Department ? Is it's value going to be
> NULL? undetermined? I need to know because I'm using such expression as:
>
> my_variable = NVL (cur_CONSTANTS%Department, 'default'), assuming that
> my_variable
> will take the value 'default' in case cur_CONSTANTS%NOTFOUND is true.
 

By fetching your cursor into CONSTANTS, which is a table name, you are implicitly creating into variables for each field in the table. Oracle makes no guarantees about the values in any of the into variables if a 'fetch' returns no rows. Only the following occurs:  

• the NO_DATA_FOUND internal exception is raised.
• SQLCODE returns +100 (Oracle error code +1403).
• SQLERRM returns the Oracle error message "no data found".
• SQL%NOTFOUND evaluates to true.
• SQL%FOUND evaluates to false.
• SQL%ROWCOUNT returns the number zero.

> Until today, cur_CONSTANTS%Department always evaluated to NULL whenever
> there was no match, but on a different system, it started returning a value,
> always the same.
 

You should find that the behavior of cur_CONSTANTS%NOTFOUND, which you mentioned using, is consistent between systems.  

• Lance