Home » Developer & Programmer » JDeveloper, Java & XML » ORA-01000: maximum open cursors exceeded (ORA-01000: maximum open cursors exceeded)
ORA-01000: maximum open cursors exceeded [message #275027] Thu, 18 October 2007 02:32
riantomulia
Messages: 1
Registered: October 2007
Location: Singapore
Junior Member
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;
    }
}

Previous Topic: XML Query Syntax Problem
Next Topic: XML DB
Goto Forum:
  


Current Time: Thu Mar 28 15:01:59 CDT 2024