This discussion is archived
5 Replies Latest reply: Jan 9, 2009 12:27 AM by 678973 RSS

ORA-01000: maximum open cursors exceeded

603903 Newbie
Currently Being Moderated
Hi All,

My application is running on SunAppServer 8.2 accessing Oracle 9i.
I have stripped my functions to a very simple module as shown below for easier debugging.
The connection to database is using JNDI referencing to the connection pooling setup SunAppServer.
I simulated 10 concurrent access to 1 simple JSP page (without any other codes, only 1 code to
access the Java method getID. I believe that the resultset and connection had been closed properly.
However, I still noticed that the open_cursor kept on going up and ultimately I get this error:
ORA-01000: maximum open cursors exceeded
It doesn't happen when I simulated only 1 user access. I can see that the open_cursor maintained at 1.
Any idea what happened? Any suggestions on what I should do to resolve the problem?


JSP
---------
<%@ page import="java.io.*,
java.lang.String"%>
<%@ page pageEncoding="UTF-8"%>
<jsp:useBean id="TBID" class="bean.TableID" scope="page"/>
<%=TBID.getID(20) + "<br>"%>

JAVA
-----------

public class ConnectionBean
{
private static Connection con = null;


public static Connection getConnection()
{
try
{
InitialContext context = new InitialContext();
//Look up our data source
DataSource ds = (DataSource) context.lookup("jdbc/connpool");
//Allocate and use a connection from the pool
con = ds.getConnection();
}
catch (Exception e)
{
System.out.println("getConnection" + e);
}
return con;
}

public synchronized static void close() throws Exception
{
if (con != null)
{
con.close();
con = null;
}
}

}

public class sqlMethod {

private static ResultSet rs=null;
private static Connection con=null;
private static Statement st=null;

public static ResultSet rsQuery(String sql)
{
try
{
con=ConnectionBean.getConnection();
st=con.createStatement();
rs=st.executeQuery(sql);


}
catch(Exception e)
{
System.out.println("sqlMethod rsQuery is abnormal"+e);
}
finally
{
close();
}
return rs;
}

public synchronized static void close()
{
try
{
if (con != null) {
ConnectionBean.close();
}
}
catch(Exception er)
{
System.out.println("con='" + con + "' close is abnormal "+er);
}

}

public synchronized static void closeStmt()
{
try
{
if (st != null) {
st.close();
st = null;
}
}
catch(Exception er)
{
System.out.println("st='" + st + "' close is abnormal "+er);
}

}
}

public class TableID {
public int getID(int iCandID) {
int iID = 0
ResultSet rs = null;
try
{
String query = "SELECT * FROM ID WHERE (FKID= " + iCandID + ")";
rs=sqlMethod.rsQuery(query);

if(rs.next()) {
iID = rs.getInt("PKID");
}
}
catch(Exception E)
{
System.err.println(E);
}
finally
{
if(rs != null)
{
try{
rs.close();
rs = null;
} catch(Exception E)
{
E.printStackTrace();
}

}
sqlMethod.closeStmt();
sqlMethod.close();
}

return iID;
}
}