Re: proc open cursor

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Mon, 13 Mar 2000 14:45:37 GMT
Message-ID: <8aiuug$rk2$1@nnrp1.deja.com>

In article <38CCA9F5.5C1DCEED_at_carinthia.com>,   Udo Apolloner <bap_at_carinthia.com> wrote:
> the hold_cursor is set to no ( proc -?) !
>
> some ideas ?
> thanks udo
>

pro*c is going to keep one open apparently.

If you set "release_cursor=yes hold_cursor=no" and test with:

main( int argc, char * argv[] )
{
char result[255];

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL CONNECT :USERID;
    printf("\nConnected to ORACLE as user: %s\n\n", USERID);

    exec sql declare c cursor for select * from dual;     exec sql open c;
    exec sql fetch c into :result;
    exec sql close c;

    exec sql declare c1 cursor for select username from all_users;     exec sql open c1;
    exec sql fetch c1 into :result;
    exec sql close c1;
    getchar();
}

you'll find the last on is the only open one. if you leave off the "release_cursor=yes hold_cursor=no", you'll find both are if maxopencursors is high enough (it is 10 by default so it should be).

That aside -- you really do want pro*c to cache these cursors for you. It is normal to have dozens, if not many many dozens of open cursors. If you run a query more then once -- this caching has already paid for itself.

--

Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Mar 13 2000 - 08:45:37 CST