Re: How do I close cursors?

A copy of this was sent to turbobutton_at_my-deja.com (if that email address didn't require changing) On Thu, 24 Jun 1999 21:16:30 GMT, you wrote:

>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.
>

it is not only possible -- its preferred and more performant. I put it in the loop to force it to go in/out of scope to demonstrate that if you do not call .close() -- you will run out of cursors eventually.

>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.

--
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 Received on Fri Jun 25 1999 - 06:13:40 CDT