maximum cursors
[173 byte] By [lucky48390a] at [2007-10-2 17:48:08] 
oh dear....I am getting a maximum cursors exceeded error... At what point in time does the cursor get created? When the PreparedStatement is set?Thanks!
You aren't closing all your JDBC resources properly. You must close all your ResultSets, and Statements
lucky48390a at 2007-7-13 19:06:07 >
I'm pretty sure that I am closing all objects properly. I've quadruple checked it after getting that same response from several people. I am re-using one statement and then closing it after I commit.
There is no ResultSet because it is insert statement only.
Here is my pseudo. please let me know if it does not seem proper
create statement
loop from 0 to array length
insert record
end loop
commit
close statement
lucky48390a at 2007-7-13 19:06:07 >
> create statement
> loop from 0 to array length
> insert record
> end loop
> commit
> close statement
Hard to say. More or less okay but it's pseudo code. Is it a vanilla insert or a stored proc? Are there any triggers attached to the table that could be fouling things up on insert?
And the last guy who had this problem and said it wasn't with his code discovered that he was leaking result sets and statements somewhere else and that was the issue.
If you really can't find it on your own I suggest getting some profiler that will show you all your objects.
lucky48390a at 2007-7-13 19:06:07 >
Just your plain old vanilla insert, using preparedStatement and bind variables. No triggers or even any indexes attached to the table...Can you recommend a profiler?
lucky48390a at 2007-7-13 19:06:07 >
> Can you recommend a profiler?You are using printStackTrace - correct?
jschella at 2007-7-13 19:06:07 >
yes. it is below. i can't really make heads or tails of it....
06/04/17 17:12:47 java.sql.SQLException: ORA-01000: maximum open cursors exceeded
06/04/17 17:12:47 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:137)
06/04/17 17:12:47 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:304)
06/04/17 17:12:47 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:271)
06/04/17 17:12:47 at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:625)
06/04/17 17:12:47 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
06/04/17 17:12:47 at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:661)
06/04/17 17:12:47 at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:951)
06/04/17 17:12:47 at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:693)
06/04/17 17:12:47 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1057)
06/04/17 17:12:47 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2901)
06/04/17 17:12:47 at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2942)
06/04/17 17:12:47 at process.Import_Emails.getSequenceVal(Import_Emails.java:428)
06/04/17 17:12:47 at process.Import_Emails.insertData(Import_Emails.java:209)
06/04/17 17:12:47 at process.Import_Emails.pullMessages(Import_Emails.java:160)
06/04/17 17:12:47 at process.Import_Emails.doGet(Import_Emails.java:81)
06/04/17 17:12:47 at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
06/04/17 17:12:47 at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
06/04/17 17:12:47 at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:65)
06/04/17 17:12:47 at oracle.security.jazn.oc4j.JAZNFilter.doFilter(Unknown Source)
06/04/17 17:12:47 at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:649)
06/04/17 17:12:47 at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:322)
06/04/17 17:12:47 at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:790)
06/04/17 17:12:47 at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:270)
06/04/17 17:12:47 at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:112)
06/04/17 17:12:47 at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:192)
06/04/17 17:12:47 at java.lang.Thread.run(Thread.java:534)
lucky48390a at 2007-7-13 19:06:07 >
> yes. it is below. i can't really make heads or tails> of it....> > 06/04/17 17:12:47 java.sql.SQLException: ORA-01000:> maximum open cursors exceeded http://forum.java.sun.com/thread.jspa?threadID=374653&start=0&tstart=0
ak79a at 2007-7-13 19:06:07 >
> yes. it is below. i can't really make heads or tails
> of it....
What it suggests to me is that the description you provided of your problem is incorrect.
I doubt that executeQuery() is doing an insert. That is part of the Oracle driver.
And your code, getSequenceVal(), doesn't look like a method name that is doing an insert.
jschella at 2007-7-13 19:06:07 >
Sorry I was away for so long!!!
getSequenceVal is querying for the next oracle sequence. It doesn't always fail there. I've looked so many times for what I'm missing, I doubt that I'm even really reading it anymore....
Please bear with me...
P.S. I am still interested in knowing at what point in time is the cursor created?
*****************************
Insert loop
*****************************
...
PreparedStatement apps = null;
Connection conn = pc.DBConnection();
OracleResultSet rset = null;
Object[] fldArry = fldList.toArray();
try
{
conn.setAutoCommit(false);
appsSql.append("INSERT INTO MY_TABLE");
appsSql.append("(ID, APP_TYPE, PREF_ZIP, WEEKDAYS, WEEKNIGHTS, ");
appsSql.append("ANYTIME, SATURDAY, SUNDAY_AM, SUNDAY_PM, OVER18, ");
appsSql.append("TRANSPORTATION) VALUES ");
appsSql.append("(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
apps= conn.prepareStatement(appsSql.toString());
for(int z = 0; z < fldArry.length; z++)
{
String[] tmpAry = fldArry[z].toString().split("\\^");
long app_sequence = getSequenceVal();
//main table insert
apps.setLong(1, app_sequence);
apps.setString(2, "PART TIME");
apps.setString(3, tmpAry[11].trim());
apps.setString(4, tmpAry[12].trim());
apps.setString(5, tmpAry[13].trim());
apps.setString(6, tmpAry[14].trim());
apps.setString(7, tmpAry[15].trim());
apps.setString(8, tmpAry[16].trim());
apps.setString(9, tmpAry[17].trim());
apps.setString(10, tmpAry[18].trim());
apps.setString(11, tmpAry[19].trim());
apps.executeUpdate();
num_imported ++;
}
apps.close();
apps= null;
conn.commit()
conn.close();
conn = null;
} //end try
....
*****************************
getSequenceVal
*****************************
...
sqlstr.append("SELECT MY_SEQUENCE.NEXTVAL FROM DUAL");
ps = conn.prepareStatement(sqlstr.toString());
rset = (OracleResultSet)ps.executeQuery();
rset.next();
sequenceval = rset.getLong(1);
ps = null;
rset.close();
rset = null;
ps.close();
ps = null;
conn.close();
conn = null;
...
lucky48390a at 2007-7-13 19:06:07 >
Anytime there's an exception, your ResultSet, Statement, and Connection won't be closed. You need to put your use of them in a try block, and the close in the associated finally block.
That's probably not what is leaking cursors (you would have to also be having exceptions), but it cold be and it's the right way to do it.
StuDerbya at 2007-7-13 19:06:07 >
>> long app_sequence = getSequenceVal();> > //main table insert> apps.setLong(1, app_sequence);Is there a reason why you do not make the sequence as part of the insert SQL directly?
jschella at 2007-7-13 19:06:07 >
> Is there a reason why you do not make the sequence as> part of the insert SQL directly?Yes. I need it in a variable, so that I can use it as a parent id in second insert statement in the loop (not written just yet). Is there a better way?
lucky48390a at 2007-7-13 19:06:07 >
> > Is there a reason why you do not make the sequence
> as
> > part of the insert SQL directly?
>
> Yes. I need it in a variable, so that I can use it as
> a parent id in second insert statement in the loop
> (not written just yet). Is there a better way?
I would use stored procs.
But at a minimum you can produce the id before you start working with the other statements (rather than doing it in the middle.)
jschella at 2007-7-13 19:06:07 >
Database Connectivity New Topic
- exception: com.mysql.jdbc.Driver error
- Java variables within SQL statements to be sent
- Avoiding duplicate entries into a table
- JDBC SQL date type conversions.
- Create Apache Derby source in Sun Application Server 8.1
- datasource retrieval that works with both tomcat and weblogic?
- JDBC MySQL connection lost
- Excel driver
Database Connectivity Hot Topic
- Error in my code?
- After ResultSet.close()
- multiple commits
- Urgent - Problem with ResultSet's next function
- Updatable ResultSet vs. Multiple Selects/Updates
- got problem of connecting to mysql database using JDBC
- ClassCasException when trying to update an oracle clob through Tomcat
- Real meaning of PooledConnection, ConnectionPoolDataSource etc.
Database Connectivity
All Classified
- Archived Forums
- Enterprise & Remote Computing
- Java Essentials
- Desktop
- Core
- Solaris Operating System
- Security
- Database Connectivity
- Java Mobility Forums
- Web & Directory Servers
- Development Tools
- Application & Integration Servers
- Java HotSpot Virtual Machine
- Other Topics
- E-Mail, Calendar, & Collaboration
- Developer Tools
- General
- Administration Tools
- Sun Hardware
- Storage Forums
- Java Enterprise System
- StarOffice
- Open Source Technologies
- BigAdmin
- Real-Time
- Java Coding