5 Replies Latest reply: Jan 9, 2009 2:27 AM by 678973 RSS

    ORA-01000: maximum open cursors exceeded

    603903
      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;
      }
      }