Re: Implicit cursors and shared pool

Hi, Sybrand

"avoid using implicit cursors as much as possible" - excellent advice - I always follow it for the most part of SELECT statements in my progs.

But, what about INSERT/DELETE statements? Oracle implicitly opens cursors for this statements too and, in the same time, Oracle does not allow us to describe cursor with such kind of statements. And, as I guess, the cursor type of "FOR UPDATE" does not avoid using implicit cursor for the following INSERT/DELETE statement with "CURRENT OF".

Have you any ideas how developers can handle this situation? And, I will be obliged if you would give me any reference to docs regarding "implicit cursors are closed but not deallocated".

Edward,
Oracle Developer,
edwardmi_at_iname.com

In article <972062655.4731.3.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> implicit cursors are closed but not deallocated.
> This is one of the reasons to avoid using implicit cursors as much as
> possible.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> "Martin Trzaskalik" <martin_at_element5.de> wrote in message
> news:8spm3r$6il$1_at_nnrp1.deja.com...
> > We have some pl/sql packages using implicit cursors to regularly
> > do updates on some tables:
> >
> > begin
> > update table set value = '123' where id = key;
> > commit;
> > end;
> >
> > Recently I found out that some of these queries appear as
> > cursors after calling SYS.DBMS_SHARED_POOL.SIZES(xxx).
> > Their amount of shared pool memory is increasing by time.
> >
> > Why is that? I guess after the procedure has been executed
> > the implicit cursor should have been closed. So I do not
> > expect him to allocated shared pool memory.
> >
> > Do I have to explicitly close these cursors?
> >
> > Any hint?
> >
> > Regards,
> >
> > Martin
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 23 2000 - 07:08:56 CDT