Re: PL/SQL and cursors. Any specialist?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/08/03
Message-ID: <3203964b.3325581_at_dcsun4>#1/1


On Fri, 02 Aug 1996 11:05:14 -0700, Stephane Chiche <Stephane_at_Chiche.com> wrote:

>HELP! PL/SQL specialist needed!
>
>Here is my problem:
>
>I've got a piece of code looking like that:
>
>Cursor cur_CONSTANTS (cur_key_1 in NUMBER) is
> select * from CONSTANTS where ((ACCOUNT_NUMBER = cur_key_1);
>
>for MCIF_10 in cur_MCIF_10 loop
>
> open cur_CONSTANTS (MCIF_10.ACCOUNT_NUMBER);
> fetch cur_CONSTANTS into CONSTANTS;
>
>.......
>
>loop;
>
>
>
>In the case MCIF_10.ACCOUNT_NUMBER isn't found in table CONSTANTS,
>cur_CONSTANTS%NOTFOUND is true, 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:
>

You never reference fields from a cursor. You can fetch a cursor into a record or set of scalars and then access the record or scalars.

If you:

	open c1;
	fetch c1 into my_record;
	dbms_output.put_line( my_record.field1 );

You have written some 'bad' code.
Since you do not know if the fetch actually did anything, the values in the record my_record are left unchanged from what they were. The fetch does NOTHING to 'my_record' if no data is fetched.

Run the following in your scott/tiger schema to see what you need to do:

declare

    cursor c1 (p_deptno number) is select * from dept where deptno = p_deptno;     my_record c1%rowtype;
    my_null_rec c1%rowtype;
begin

    open c1(10);
    fetch c1 into my_record;
    dbms_output.put_line( my_record.dname );     close c1;  

    open c1(100);
    fetch c1 into my_record;
    dbms_output.put_line( my_record.dname );     close c1;  

    dbms_output.put_line( '----------------------' );
 

    open c1(10);
    fetch c1 into my_record;
    dbms_output.put_line( my_record.dname );     close c1;  

    my_record := my_null_rec;
    open c1(100);
    fetch c1 into my_record;
    dbms_output.put_line( my_record.dname );     close c1;
end;

account
account



account  

PL/SQL procedure successfully completed.  

SQL> Basically, you either NEED TO LOOK FOR A NOT FOUND (preferred really, this is the correct way to do this) or you need to set the record to some known value (the line my_record := my_null_rec does this since a declare will always set the fields to NULL unless you default it) before fetching into it.

>my_variable = NVL (cur_CONSTANTS%Department, 'default'), assuming that
>my_variable
>will take the value 'default' in case cur_CONSTANTS%NOTFOUND is true.
>
>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. Is it
>normal? I don't
>see anything in the documentation about this.
>
>So, NULL or Undertermined?

neither actually.... It might be NULL, it might not. It is definitely not undetermined tho.

The behaviour you are seeing on the one system must be because the FIRST fetches failed (and the record was null) and hence the values fetched appeared to be null. One the second system, the FIRST fetch must have been successful and you will keep getting that value forever on subsuquent unsuccessful fetches (that you do no error checking on), the record would never get reset.

It is the result of having different data on the systems that is causing your problem, not different pl/sql behaviour.

>
>I know I could test cur_CONSTANTS%NOTFOUND, but this is not the point.
>
>Thank you in advance.
>
>Please e-mail me at stephane_at_chiche.com

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database


statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Sat Aug 03 1996 - 00:00:00 CEST

Original text of this message