Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Twin.

Asked: April 23, 2002 - 3:27 pm UTC

Last updated: April 23, 2002 - 6:29 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Tom,

Thanks for taking time to answer the questions. I have question about the implict cursors are not closed after the Java PrepareCall statement is called. Following is the test case to illustrate the issue (I'm modified your example somewhat to demostrate),

create or replace package demo_pkg
as
type refcur is ref cursor;

procedure get_cur( x in out refcur );
end;
/

create or replace package body demo_pkg
as

procedure get_cur( x in out refcur )
is

l_user varchar2(1000);

begin

open x for select USER from dual;

select user
into l_user
from dual where rownum = 1;

select user
into l_user
from dual where rownum = 1;

end;

end;
/

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;


class curvar
{
public static void showOpenCnt( Connection conn, String msg)
throws SQLException
{
Statement stmt = conn.createStatement();
ResultSet rset =
stmt.executeQuery
( "select a.value, b.name "+
"from v$mystat a, v$statname b "+
"where a.statistic# = b.statistic# "+
"and a.statistic#= 3");
System.out.println( "====================================" );
System.out.println( msg );
System.out.println( "====================================" );
System.out.println( " " );

while( rset.next() )
System.out.println( rset.getString(1)+ " " +
rset.getString(2) );
System.out.println( "-----------------------" );
rset.close();
stmt.close();

Statement stmt1 = null;
ResultSet rset1 = null;

stmt1 = conn.createStatement();
rset1 = stmt1.executeQuery("select sid, sql_text from v$open_cursor where sid = (select a.sid "+
"from v$mystat a, v$statname b "+
"where a.statistic# = b.statistic# "+
"and a.statistic#= 3 "+
"and rownum = 1)");

System.out.println("Open Cursors Currently");
while (rset1.next()) {
System.out.println("SID***" + rset1.getInt(1) + " " + rset1.getString(2) );
}
System.out.println( "-----------------------" );
System.out.println( " " );
rset1.close();
stmt1.close();
}


public static void main (String args [])
throws SQLException, ClassNotFoundException
{
String query =
"begin demo_pkg.get_cur( :1 ); end;";

DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());

Connection conn=
DriverManager.getConnection
("jdbc:oracle:oci8:@ora8idev",
"scott", "tiger");

showOpenCnt( conn, "Before Anything" );

CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();

showOpenCnt( conn, "After prepare and execute" );

ResultSet rset = (ResultSet)cstmt.getObject(1);
showOpenCnt( conn,
"After prepare and execute and getObject" );

for(int i = 0; rset.next(); i++ )
{
if ( i == 10 )
showOpenCnt(conn,"After fetching 10 rows...");
}

showOpenCnt( conn, "After but before any close" );

rset.close();
showOpenCnt( conn, "After ResultSet closes" );

cstmt.close();
showOpenCnt( conn, "After CallableStatement closes" );

}
}

Here is the results,


C:\demo>java curvar
====================================
Before Anything
====================================

1 opened cursors current
-----------------------
Open Cursors Currently
SID***52 select sid, sql_text from v$open_cursor where sid = (select
-----------------------

====================================
After prepare and execute
====================================

5 opened cursors current
-----------------------
Open Cursors Currently
SID***52 select sid, sql_text from v$open_cursor where sid = (select
SID***52 SELECT USER FROM DUAL WHERE ROWNUM = 1
SID***52 SELECT USER FROM DUAL WHERE ROWNUM = 1
SID***52 begin demo_pkg.get_cur( :1 ); end;
SID***52 SELECT USER FROM DUAL
-----------------------

====================================
After prepare and execute and getObject
====================================

5 opened cursors current
-----------------------
Open Cursors Currently
SID***52 select sid, sql_text from v$open_cursor where sid = (select
SID***52 SELECT USER FROM DUAL WHERE ROWNUM = 1
SID***52 SELECT USER FROM DUAL WHERE ROWNUM = 1
SID***52 begin demo_pkg.get_cur( :1 ); end;
SID***52 SELECT USER FROM DUAL
-----------------------

====================================
After but before any close
====================================

5 opened cursors current
-----------------------
Open Cursors Currently
SID***52 select sid, sql_text from v$open_cursor where sid = (select
SID***52 SELECT USER FROM DUAL WHERE ROWNUM = 1
SID***52 SELECT USER FROM DUAL WHERE ROWNUM = 1
SID***52 begin demo_pkg.get_cur( :1 ); end;
SID***52 SELECT USER FROM DUAL
-----------------------

====================================
After ResultSet closes
====================================

4 opened cursors current
-----------------------
Open Cursors Currently
SID***52 select sid, sql_text from v$open_cursor where sid = (select
SID***52 SELECT USER FROM DUAL WHERE ROWNUM = 1
SID***52 SELECT USER FROM DUAL WHERE ROWNUM = 1
SID***52 begin demo_pkg.get_cur( :1 ); end;
-----------------------

====================================
After CallableStatement closes
====================================

3 opened cursors current
-----------------------
Open Cursors Currently
SID***52 select sid, sql_text from v$open_cursor where sid = (select
SID***52 SELECT USER FROM DUAL WHERE ROWNUM = 1
SID***52 SELECT USER FROM DUAL WHERE ROWNUM = 1
-----------------------

As you can see the two identical "SELECT USER FROM DUAL WHERE ROWNUM = 1" SQL statements in the procedure are still open after the PrepareCallstatement closed.

So, is it correct? Could you explain and help to solve the problem since if there is loop and OPEN_CURSURS set to 500 or 1000, it is easy to get ORA-01000 error.

Thanks

Twin Dophin.



and Tom said...

These are cached by PLSQL for performance.

They will remain there until you run out of slots in your OPEN CURSOR array -- at which point they are flushed if not currently being used (plsql lets them "go away" if and when the server needs that slot)

They do not count against you, they are there for performance. It is an EXCELLENT reason why most java programs entire suite of SQL should consist of nothing more then begin .... end; -- never any actual DML of its own. More manageable, more flexible.

You can test this out yourself by using this:

create or replace package demo_pkg
as
type refcur is ref cursor;

procedure get_cur( x in out refcur );
end;
/

create or replace package body demo_pkg
as

g_first_time boolean default true;

procedure get_cur( x in out refcur )
is

l_user varchar2(1000);

begin

open x for select USER from dual THIS_IS_A_JAVA_CURSOR;

if ( g_first_time )
then

select user
into l_user
from dual THIS_IS_PLSQL where rownum = 1;

select user
into l_user
from dual THIS_TOO_IS_PLSQL where rownum = 1;
g_first_time := false;
end if;
end;

end;
/

that plsql only needs the cursors for a bit -- we don't need them everytime...

Now I modified the java to be:

public static void main (String args [])
throws SQLException, ClassNotFoundException
{
String query =
"begin demo_pkg.get_cur( :1 ); end;";

DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());

Connection conn=
DriverManager.getConnection
("jdbc:oracle:oci8:@ora817dev",
"scott", "tiger");

showOpenCnt( conn, "Before Anything" );

CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);

for( int j = 0; j < 100; j++ )
{
cstmt.execute();
showOpenCnt( conn, j + ") After prepare and execute" );

ResultSet rset = (ResultSet)cstmt.getObject(1);

for(int i = 0; rset.next(); i++ );
}

cstmt.close();
showOpenCnt( conn, "After CallableStatement closes" );
}


I don't close the result sets - we just let them leak all over the place. I have open_cursors set to 50 and run:


> !java
java curvar
====================================
Before Anything
====================================

1 opened cursors current
-----------------------
Open Cursors Currently
SID***8 SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='
SID***8 select a.value, b.name from v$mystat a, v$statname b where a
SID***8 ALTER SESSION SET NLS_TERRITORY = 'AMERICA'
SID***8 ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'
SID***8 select sid, sql_text from v$open_cursor where sid = (select
SID***8 ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'A
-----------------------

====================================
0) After prepare and execute
====================================

5 opened cursors current
-----------------------
Open Cursors Currently
SID***8 SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='
SID***8 select a.value, b.name from v$mystat a, v$statname b where a
SID***8 SELECT USER FROM DUAL THIS_IS_PLSQL WHERE ROWNUM = 1
SID***8 begin demo_pkg.get_cur( :1 ); end;
SID***8 ALTER SESSION SET NLS_TERRITORY = 'AMERICA'
SID***8 ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'
SID***8 select sid, sql_text from v$open_cursor where sid = (select
SID***8 ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'A
SID***8 SELECT USER FROM DUAL THIS_TOO_IS_PLSQL WHERE ROWNUM = 1
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
-----------------------

====================================
1) After prepare and execute
====================================

6 opened cursors current
-----------------------
Open Cursors Currently
SID***8 SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='
SID***8 select a.value, b.name from v$mystat a, v$statname b where a
SID***8 SELECT USER FROM DUAL THIS_IS_PLSQL WHERE ROWNUM = 1
SID***8 begin demo_pkg.get_cur( :1 ); end;
SID***8 ALTER SESSION SET NLS_TERRITORY = 'AMERICA'
SID***8 ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'
SID***8 select sid, sql_text from v$open_cursor where sid = (select
SID***8 ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'A
SID***8 SELECT USER FROM DUAL THIS_TOO_IS_PLSQL WHERE ROWNUM = 1
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
-----------------------

note that after each iteration I got more and more "this is a java cursor". The plsql guys stayed in there.... UNTIL:


====================================
45) After prepare and execute
====================================

50 opened cursors current
-----------------------
Open Cursors Currently
SID***8 select a.value, b.name from v$mystat a, v$statname b where a
SID***8 SELECT USER FROM DUAL THIS_IS_PLSQL WHERE ROWNUM = 1
SID***8 begin demo_pkg.get_cur( :1 ); end;
SID***8 select sid, sql_text from v$open_cursor where sid = (select
SID***8 ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'A
SID***8 SELECT USER FROM DUAL THIS_TOO_IS_PLSQL WHERE ROWNUM = 1
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
<lots of those chopped out>
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
-----------------------

====================================
46) After prepare and execute
====================================

49 opened cursors current
-----------------------
Open Cursors Currently
SID***8 select a.value, b.name from v$mystat a, v$statname b where a
SID***8 begin demo_pkg.get_cur( :1 ); end;
SID***8 select sid, sql_text from v$open_cursor where sid = (select
SID***8 ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'A
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
<lots chopped NOTE: PLSQL cursors *gone*>
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
-----------------------

====================================
47) After prepare and execute
====================================

50 opened cursors current
-----------------------
Open Cursors Currently
SID***8 select a.value, b.name from v$mystat a, v$statname b where a
SID***8 begin demo_pkg.get_cur( :1 ); end;
SID***8 select sid, sql_text from v$open_cursor where sid = (select
SID***8 ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'A
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
...
SID***8 SELECT USER FROM DUAL THIS_IS_A_JAVA_CURSOR
-----------------------

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

at java.lang.Throwable.<init>(Compiled Code)
at java.lang.Exception.<init>(Compiled Code)
at java.sql.SQLException.<init>(Compiled Code)
at oracle.jdbc.dbaccess.DBError.throwSqlException(Compiled Code)
at oracle.jdbc.oci8.OCIDBAccess.check_error(Compiled Code)
at oracle.jdbc.oci8.OCIDBAccess.parseExecuteDescribe(Compiled Code)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(Compiled Code)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(Compiled Code)
at oracle.jdbc.driver.OracleStatement.executeQuery(Compiled Code)
at curvar.showOpenCnt(Compiled Code)
at curvar.main(Compiled Code)


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Open Cursor Issue

A reader, April 23, 2002 - 6:29 pm UTC

Tom,

Great, I tested here. it works as you said.

Thanks


Open cursor issue on jdbc driver

Albert Nelson A, October 11, 2005 - 6:33 am UTC

Hi Tom,

A related question on open cursors:

When using the thin jdbc driver, the statement closing behaviour seems to vary from between the versions of jdbc driver. In the jdbc version 9.2.0.3 the cursor is closed immediately after statement is closed whereas in version 10.2.0.1 it is not closed. Is it an enhancement? How will this affect the performance?

Java program:
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class OpenCursorTest {

public static void displayMetaData(Connection conn) {
try {
PrintStream out = System.out;

DatabaseMetaData meta = conn.getMetaData();

// gets driver info:
out.println("Database Product Name is ... "
+ meta.getDatabaseProductName());
out.println("Database Product Version is "
+ meta.getDatabaseProductVersion());
out.println("\nJDBC Driver Name is ........ " + meta.getDriverName());
out.println("JDBC Driver Version is ..... " + meta.getDriverVersion());
out.println("JDBC URL " + meta.getURL());

// get environment info:
java.util.Properties props = System.getProperties();
java.util.Enumeration propNames = props.propertyNames();

StringBuffer classpath = new StringBuffer("\t"
+ props.getProperty("java.class.path"));
int idx2 = 0;

while (idx2 < classpath.length()) {
idx2 = classpath.toString().indexOf(";", idx2);

if (idx2 == -1)
break;
classpath.replace(idx2, ++idx2, "\n\t");

idx2++;
}
out.println("\njava runtime Version: \t"
+ props.getProperty("java.runtime.version"));

out.println("java vm Version: \t" + props.getProperty("java.vm.version"));
out.println("java vm name: \t" + props.getProperty("java.vm.name"));
out.println("\njava classpath: " + classpath + "\n");
} catch (Exception e) {
e.printStackTrace();
}

}
public static int getSid(Connection conn) throws SQLException {
PreparedStatement ps = conn
.prepareStatement("select sid from v$mystat where rownum = 1");
ResultSet rs = ps.executeQuery();
rs.next();
int sid = rs.getInt(1);
rs.close();
ps.close();
return sid;
}
public static void showOpenCursors(int sid, String msg) throws SQLException {
System.out.println("=============");
System.out.println(msg);
System.out.println("=============");
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@10.2.40.60:1521:citynet2", "scott", "tiger");

PreparedStatement ps = conn.prepareStatement("select sid, sql_text " + //
"from v$open_cursor c " + //
"where c.sid = ?" //
);

ps.setInt(1, sid);

ResultSet rs = ps.executeQuery();

while (rs.next())
System.out.println(rs.getInt(1) + " " + rs.getString(2));

rs.close();
ps.close();

}

public static void main(String[] args) throws SQLException {

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@10.2.40.60:1521:citynet2", "scott", "tiger");
displayMetaData(conn);
int sid = getSid(conn);
showOpenCursors(sid, "Initial");
PreparedStatement ps = conn.prepareStatement("select 'Test' from dual");
ResultSet rs = ps.executeQuery();
rs.close();
showOpenCursors(sid, "Before closing statement");
ps.close();
showOpenCursors(sid, "After closing statement");
conn.close();
}
}

Output with JDBC driver 9.2.0.3 in the classpath:
Database Product Name is ... Oracle
Database Product Version is Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64b
it Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 9.2.0.3.0
JDBC URL jdbc:oracle:thin:@10.2.40.60:1521:citynet2

java runtime Version: 1.4.1_05
java vm Version: 8.1sp2-1.4.1_05-Load10-viking-win32-sthqa29-20031105-155
4
java vm name: BEA WebLogic JRockit(R) Virtual Machine

java classpath: .
ojdbc14_9203.jar

=============
Initial
=============
=============
Before closing statement
=============
90 select 'Test' from dual
=============
After closing statement
=============

Output with 10.2.0.1 JDBC driver in the classpath:
Database Product Name is ... Oracle
Database Product Version is Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64b
it Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 10.2.0.1.0
JDBC URL jdbc:oracle:thin:@10.2.40.60:1521:citynet2

java runtime Version: 1.4.1_05
java vm Version: 8.1sp2-1.4.1_05-Load10-viking-win32-sthqa29-20031105-155
4
java vm name: BEA WebLogic JRockit(R) Virtual Machine

java classpath: .
ojdbc14_10201.jar

=============
Initial
=============
57 select sid from v$mystat where rownum = 1
=============
Before closing statement
=============
57 select 'Test' from dual
=============
After closing statement
=============
57 select 'Test' from dual

Thanks in advance.

Regards,

Albert Nelson A.



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library