Re: How do I close cursors?

From: <turbobutton_at_my-deja.com>
Date: Thu, 24 Jun 1999 21:16:30 GMT
Message-ID: <7ku774$his$1@nnrp1.deja.com>

However, it is possible to throw an exception in your execute(String sql) and never close the statement. put each line into a seperate try catch so you do not get thrown out without closing.

Also does this not insist that a stored procedure be 'prepared' every loop, ie...

for( int i = 0; i <= 2000; i++)
{
CallableStatement NewStmt = conn.prepareCall("{call insertstuf(?,?)}" );

newStmt.setInt(1,"Billy");
newStmt.setString(2,"Joe");
newStmt.executeUpdate();
NewStmt.close();

}
or is it possible to get away with placing the prepareCall line outside loop.

thanks in advance,
aboston

> Is it possible that you are calling lots of routines that each
declare their own
> "Statement" variables and let these go out of scope without
calling .close()?
>
> For example, i think I am able to replicate your issue with the
following code.
>
> I have a table T ( x number );
> my open_cursors is set to 1,000
>
> the following code works:
>
> import java.sql.*;
> import oracle.jdbc.driver.*;
>
> class test {
> public static void main (String args []) throws SQLException {
>
> // Load Oracle driver
> DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver
());
>
> // Connect to the local database
> Connection conn =
> //DriverManager.getConnection

("jdbc:oracle:thin:@slackdog:1521:oracle8",
> DriverManager.getConnection ("jdbc:oracle:oci8:@slackdog.world",
> "scott", "tiger");
>
> // Query the employee names
> Statement stmt = conn.createStatement ();
>
> stmt.execute( "delete from t" );
>
> for( int i = 0; i <= 2000; i++)
> {
> Statement NewStmt = conn.createStatement ();
>
> NewStmt.execute( "INSERT INTO T VALUES (" + i + ")" );
> NewStmt.close();
> }
> }
> }
>
> **but** if you comment out the NewStmt.close(); inside the loop, it
fails with
> max open cursors after a while. That is because the statement is
just going out
> of scope inside the loop for each iteration -- and we have not closed
the
> cursor. As there are no destructors in java, we can't clean up cursors
> automagically -- java will clean up unused memory but it won't clean
up
> 'everything' possible.
>
> Are you ever closing statements?
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated
June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 24 1999 - 16:16:30 CDT