Your code looks perfectly fine. You are properly closing the statement,
which is where the opening of the Oracle cursor is initiated. Do you
have many threads accessing different bean instances simultaneously? If
so, you might be exceeding the maximum open cursors because of the
number of concurrent threads. If this is the case, the only way to fix
this is to either reduce the number of concurrent threads, or increase
the MAX_OPEN_CURSORS parameter of your Oracle database. If you are
seeing this as a result of running a single thread over and over, it
might be a bug in the JDBC driver you are using. Make sure you are
using the latest version of the thin driver available on the Oracle web
site.
Scot.
Bill Burke wrote:
> I'm not using PreparedStatement though, I'm using just a regular
> Statement. Does JBoss pool Statements? Why does it do this if it does?
>
> Bill
>
> Ari Suutari wrote:
>
>> Hi,
>>
>> If you are sure that you close resultset and statement this should not
>> happen, maybe Minerva is pooling prepared statements, I remember
>> some discussions about having limit for such cached statetements.
>>
>> Ari S.
>>
>> ----- Original Message ----- From: "Bill Burke"
>> <[EMAIL PROTECTED]>
>> To: "JBoss-User" <[EMAIL PROTECTED]>
>> Sent: Tuesday, March 06, 2001 5:52 AM
>> Subject: Re: [jBoss-User] maximum open cursors exceeded
>>
>>
>>> I tried closing the ResultSet before the Statement and still, after
>>> awhile, I get maximum open cursors exceeded. Anybody else run into
>>> this problem with regular EJBs?
>>>
>>> Thanks,
>>> Bill
>>>
>>> Bill Burke wrote:
>>>
>>>> I don't know if this is wrong to do, but I'm using an Oracle
>>>> connection pool that I created for my EJB's to execute SQL queries
>>>> and updates. The problem is, after awhile I get a "maximum open
>>>> cursors exceeded" error from Oracle. I'm running on RedHat linux
>>>> 6.2 with an Oracle DB running on NT. I'm using the
>>>> jdbc:oracle:thin drivers.
>>>>
>>>> jboss.jcml:
>>>> <mbean name="DefaultDomain:service=XADataSource,name=oraclePool">
>>>> ....
>>>> </mbean>
>>>>
>>>> Java code is below.
>>>> Some things to note, executeSQLUpdate and executeSQLQuery. I can't
>>>> seem to find out where I'm not cleaning up connections. Do you
>>>> have to close the ResultSet? The JDBC docs say that if you close
>>>> the Statement, then your result sets will be closed as well.
>>>>
>>>> Thanks in advance,
>>>> Bill
>>>>
>>>> Java Code:
>>>>
>>>>
>>>> import java.io.*;
>>>> import java.rmi.*;
>>>> import javax.ejb.*;
>>>> import java.util.*;
>>>> import javax.naming.*;
>>>> import com.ctone.services.utils.*;
>>>> import com.ctone.services.constants.*;
>>>> import com.ctone.services.framework.*;
>>>> import javax.sql.*;
>>>> import java.sql.*;
>>>>
>>>> public class CTSQLServiceBean extends CTServiceBean
>>>> {
>>>> public CTSQLServiceBean()
>>>> {
>>>> super();
>>>> }
>>>>
>>>> private Connection getConnection() throws Exception
>>>> {
>>>> javax.sql.DataSource source =
>>>> (javax.sql.DataSource)getInitialContext().lookup("java:/oraclePool");
>>>> return source.getConnection();
>>>> }
>>>>
>>>> public int executeSQLUpdate(String sqlUpdate) throws RemoteException
>>>> {
>>>> Connection connect = null;
>>>> Statement statement = null;
>>>> int rowCount = 0;
>>>>
>>>> try
>>>> {
>>>> connect = getConnection();
>>>> if (connect == null)
>>>> {
>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>> "Unable to connect to database." );
>>>> }
>>>> ServicesLog.log( 5, "Got connection, creating statement..." );
>>>>
>>>> statement = connect.createStatement();
>>>> if (statement == null)
>>>> {
>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>> "Unable to create SQL statement for: '" +
>>>> sqlUpdate + "'" );
>>>> }
>>>> ServicesLog.log( 5, "Created statement, executing query..." );
>>>>
>>>> rowCount = statement.executeUpdate( sqlUpdate );
>>>> if (rowCount == 0)
>>>> {
>>>> ServicesLog.log( 5, "No rows affected by SQL statement '" +
>>>> sqlUpdate + "'" );
>>>> }
>>>> else
>>>> {
>>>> ServicesLog.log( 5, String.valueOf( rowCount ) + " rows
>>>> affected by SQL statement..." );
>>>> }
>>>> }
>>>> catch (SQLException sqe)
>>>> {
>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>> "Unable to execute SQL query: '" + sqlUpdate + "'",
>>>> sqe );
>>>> }
>>>> catch (Exception ex)
>>>> {
>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>> "Unable to get connection pool Driver.",
>>>> ex );
>>>> }
>>>> finally
>>>> {
>>>> if (statement != null)
>>>> {
>>>> try
>>>> {
>>>> statement.close();
>>>> }
>>>> catch (SQLException sqe)
>>>> {
>>>> }
>>>> }
>>>>
>>>> if (connect != null)
>>>> {
>>>> try
>>>> {
>>>> connect.close();
>>>> }
>>>> catch (SQLException sqe)
>>>> {
>>>> }
>>>> }
>>>> }
>>>>
>>>> return rowCount;
>>>> }
>>>>
>>>> public CTResultSet executeSQLQuery(String sqlQuery) throws
>>>> RemoteException
>>>> {
>>>> Connection connect = null;
>>>> Statement statement = null;
>>>> CTResultSet results = null;
>>>>
>>>> try
>>>> {
>>>> connect = getConnection();
>>>> if (connect == null)
>>>> {
>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>> "Unable to connect to database." );
>>>> }
>>>> ServicesLog.log( 5, "Got connection, creating statement..." );
>>>>
>>>> statement = connect.createStatement();
>>>> if (statement == null)
>>>> {
>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>> "Unable to create SQL statement for query: '" +
>>>> sqlQuery + "'" );
>>>> }
>>>> ServicesLog.log( 5, "Created statement, executing query..." );
>>>>
>>>> ResultSet resultSet = statement.executeQuery( sqlQuery );
>>>> if (resultSet == null)
>>>> {
>>>> ServicesLog.log( 5, "No results returned for query '" +
>>>> sqlQuery + "'" );
>>>> }
>>>> else
>>>> {
>>>> ServicesLog.log( 5, "Got results from query..." );
>>>> results = new CTResultSet( resultSet );
>>>> }
>>>> }
>>>> catch (SQLException sqe)
>>>> {
>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>> "Unable to execute SQL query: '" + sqlQuery + "'",
>>>> sqe );
>>>> }
>>>> catch (Exception ex)
>>>> {
>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>> "Unable to get connection pool Driver.",
>>>> ex );
>>>> }
>>>> finally
>>>> {
>>>> if (statement != null)
>>>> {
>>>> try
>>>> {
>>>> statement.close();
>>>> }
>>>> catch (SQLException sqe)
>>>> {
>>>> }
>>>> }
>>>>
>>>> if (connect != null)
>>>> {
>>>> try
>>>> {
>>>> connect.close();
>>>> }
>>>> catch (SQLException sqe)
>>>> {
>>>> }
>>>> }
>>>> }
>>>>
>>>> return results;
>>>> }
>>>> }
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> --------------------------------------------------------------
>>>> To subscribe: [EMAIL PROTECTED]
>>>> To unsubscribe: [EMAIL PROTECTED]
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>> --
>>> --------------------------------------------------------------
>>> To subscribe: [EMAIL PROTECTED]
>>> To unsubscribe: [EMAIL PROTECTED]
>>>
>>
>>
>>
>> --
>> --------------------------------------------------------------
>> To subscribe: [EMAIL PROTECTED]
>> To unsubscribe: [EMAIL PROTECTED]
>>
>>
>>
>>
>
>
>
>
> --
> --------------------------------------------------------------
> To subscribe: [EMAIL PROTECTED]
> To unsubscribe: [EMAIL PROTECTED]
>
>
--
--------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
