Re: Strange cursor behavior?

jocave_at_yahoo.com (Justin Cave) writes:

> Can you do the sanity check of "select count(*) from dual"?
>
> "Vasileios Lourdas" <lourdas_at_otenet.gr> wrote in message news:<au7slq$9id$1_at_nic.grnet.gr>...
> > Hello,
> >
> > When executing this PL/SQL code snippet in Oracle 8.1.6 and Oracle 9.0.1:
> > ***
> > declare
> > c pkg.c_type;
> > c_rec dual%rowtype;
> >
> > begin
> > open c for 'select dummy from dual';
> > loop
> > exit when c%notfound;
> > fetch c into c_rec;
> > dbms_output.put_line('c is ' || c_rec.dummy);
> > end loop;
> > close c;
> > end;
> > ***
> > i get the following results:
> > ***
> > SQL> /
> > c is X
> > c is X
> >
> > PL/SQL procedure successfully completed
> >
> > SQL>
> > ***
> > c_type is a type of a ref cursor declared in a package spec pkg. However,
> > since the DUAL table has only one row, why the loop executes twice before it
> > ends?
> >
> > I'm sorry if i miss something obvious, but this came across today and i've
> > been thinking about it...
> >
> > Thanks,
> > Vasilis

I think its to do with the order of your test and the fetch. The problem is you are first testing with the c%notfound before you have done any fetch and the value is undefined (NULL). You then do a fetch and print the result, go back around where c%notfound is false because data was found in the last fetch, do the second fetch (which returns nothing) and then print out the old value from c_rec (from the first fetch). Try changing your code to

declare
 c pkg.c_type;
 c_rec dual%rowtype;
begin

   open c for 'select dummy from dual';
   fetch c into c_rec;
   loop

     exit when c%notfound;
     dbms_output.put_line('c is ' || c_rec.dummy);
     fetch c into c_rec;

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!