Re: oracle leaving open cursors...

From: steve (me_at_me.com)
Date: 04/01/04 

Date: Thu, 1 Apr 2004 17:16:44 +0800

On Thu, 25 Mar 2004 16:50:49 +0800, asaguden wrote
(in article <b190197d.0403250050.4231f627@posting.google.com>):

> Hi,
> We have a database driven servlet/jsp application with Oracle as backend.
> We are using oracle classes12.zip driver for our jdbc connections.
>
> At runtime, our connection pool reports that there are no
> used connections, but there are usually appr. 600 open cursors.
>
> How can that be?
> What exactly is a cursor after I hav used it (gotten data from it)?
> What resources are these cursors holding?
>
>
> When is a cursor closed (in oracle), when I close the statement,
> when I close the connection, or do I always have to close the cursor(s)
> explicitely?
>
> I am puzzled...
>
> / Peter

it depends "how" you are using the cursor, if you are calling a pl/sql
routine that returns a cursor, then the cursor is going to stay open for the
duration that the connection is open, irrelevant of the fact that you have
closed the transfer protocol.

but to decide that i would need to see exactly what sql statment you are
executing.

if it is just a straight:
 "select * from emp order by 1", then what you are doing will function
correctly.

BUT if it is
"?=returnacursor();"

where ? is a cursor tied to oracle.CURSOR in your java program.
& "returnacursor()" is a pl/sql subroutine that returns a cursor,then you
need to remember that if you have opened a cursor inside the pl/sql to pass
back to the java, then you may need a routine that closes the pl/sql cursor,
resident in the same pl/sql package.

Relevant Pages

  • Re: Huge memory comsumption of ADODB Connection object
    ... Why I said the Connection ... to nothing, the memory used by my app doesn't decrease, only when I close the ... I take the same opinion as you do, if the cursor doesn't return ... resultset to client, no impact will be imposed to client. ...
    (microsoft.public.data.ado)
  • Re: VB6 ADO DB2 - Update Problem
    ... Jeweils ein Feld der beiden ... Was veranlasst Dich zu der Annahme, dass Daten eines Recordsets mit ... Man kann eine Connection auch ohne ein solches Ungetüm öffnen, ... Warum serverseitiger Cursor? ...
    (microsoft.public.de.vb.datenbank)
  • Re: add a new range partition in a existing table
    ... i want add a new range partition in a existing table? ... CURSOR context_date_cur IS ... Where in any Oracle doc did you ever see double quotes like this? ... And what about this requires PL/SQL or a CURSOR LOOP? ...
    (comp.databases.oracle.misc)
  • Re: psycopg, transactions and multiple cursors
    ... >> opening a connection thus the app doesn't suffer so much. ... first time a change is made to the database. ... >> what you want is a nested and not a new cursor. ... B e careful that you don;t confuse the DB API curosrs with the cursors ...
    (comp.lang.python)
  • Re: Please help with cmd.execute select SQL?
    ... > What is the proper format for my SQL str using command.execute? ... RecordCount requires the use of an expensive cursor. ... and connection immediately, releasing the connection back to the connection ...
    (microsoft.public.inetserver.asp.general)