ORA-01000: maximum open cursors exceeded

[allfororacle]

ORA-01000: maximum open cursors exceeded

this is the error which i am getting when i try to create tables and load the data dynamically into the oracle8i database.

anythoghts... appreciated.
thx in advance.
anu.

[Anon]

You are trying to open too many cursors concurrently. Usually this isn't a problem since most Oracle DBA immediately increase OPEN_CURSORS from the default 50 as soon as they install the database. Numbers like 255 to 400 are pretty normal for reasonably active systems.

Most of the time when someone gets that error, it means that their code is not properly managing/closing cursors. Does this happen with a particular piece of code? If so, can you post it?

-- Michael
Darkstreak Consulting
www.darkstreak.com

[allfororacle]

/* ---------------------------------------------------------------------
Check and see if a table exists.
Input:	Database connection object
	Table name
Ouput:	true/false
--------------------------------------------------------------------- */
public static boolean isTableExists(Connection dbc,
			String sTableName) throws SQLException {
	Statement st = null;
	ResultSet rs = null;

	try {
	st = dbc.createStatement();
  rs = st.executeQuery(getSQLTableExists(sTableName));
 	if (rs.next()) return true;
	} 
	catch (SQLException se) {
		throw se;
	}
	finally {
		try { rs.close(); } catch (Exception ignore) {}
		try { st.close(); } catch (Exception ignore) {}
	}
	return false;
}

//
this is the code module of a java class (TableManager.java) which creates a new table.
//

============================================
// Create new table.
if (!TableManager.isTableExists(dbc, sTableName))
{
System.out.println("TABLENAME*" + sTableName);
try {
if (log.isDebugEnabled()){
log.debug("Create table " + sTableName + " " );
TableManager.createTable(dbc, sTableName, sWireCenter, sBay,
sShelf, sPort, sDt, sMeFunc,sAcna,
sCkid, sOrderNum, sDueDt,sInvType,sInvSystem,
sInvEntity);
}
}catch (SQLException se) {
if (log.isDebugEnabled()) {
log.debug(sTableName
+ " ORA-" + se.getErrorCode() + ": "
+ sBuffer );
se.printStackTrace();
}
}
}

 // Prepare SQL statements.
 psInsert = _dbc.prepareStatement(getSQLInsert());
  try {
        psInsert.setString(1,  sWireCenter);
					psInsert.setString(2,  sBay);
					psInsert.setString(3,  sShelf);
        psInsert.setString(4,  sPort);
        if(sDt == null)
        psInsert.setNull  (5, java.sql.Types.DATE);
        else
        psInsert.setString(5,  sDt);
					psInsert.setString(6,  sSpcFunc);
        psInsert.setString(7,  sAcna);
        if(sCkid == null)
        psInsert.setNull  (8,  java.sql.Types.INTEGER);
        else
        psInsert.setString(8, sCkid);
        psInsert.setString(9, sOrderNum);
        if(sDueDt == null)
        psInsert.setNull  (10, java.sql.Types.DATE);
        else
        psInsert.setString(10, sDueDt);
					psInsert.setString(11, sInvType);
				  psInsert.setString(12, sInvSystem);
        psInsert.setString(13, sInvEntity);
        psInsert.executeUpdate();
        ++lInsert;
      }catch (SQLException se) {
				if (_log.isDebugEnabled()) {
					_log.debug(_sTableName
						+ " ORA-" + se.getErrorCode() + ": "
					        + sBuffer );
					 se.printStackTrace();
         }
				++lDiscard;
				continue;
      }
			if ((lInsert + lUpdate) % 1000 == 0) _dbc.commit();
		} // while
	 }catch (Exception e) {
		_sExceptionMessage = e.getMessage();
  e.printStackTrace();

}
finally {
log.info(sTableName + " "
+ lInsert + " inserted, "
+ lUpdate + " updated, "
+ lDiscard + " discarded.");
try { _dbc.commit(); } catch (Exception ignore) {}
try { psInsert.close(); } catch (Exception ignore) {}
try { psUpdate.close(); } catch (Exception ignore) {}
try { psSelect.close(); } catch (Exception ignore) {}
try { vColumns.removeAllElements(); } catch (Exception ignore) {}
try { vDates.removeAllElements(); } catch (Exception ignore) {}
} // try

this code is to call the createtable method , create and load the table with data.
but all my rs, stmts and prepared stmts are closed perfectly.

Any thoughts...

Thx,
Anu.

[Anon]

My first thought is that this is a PHP forum not a Java forum. Assuming you are calling this Java code from PHP, could you post the PHP code as well?

My second thought was that the code sample isn't complete. There missing code appears to be the top of an enclosing looping structure.

-- Michael
Darkstreak Consulting
www.darkstreak.com

[Anon]

The problem is in the isTableExists function.

If the table exists, it returns true without closes either the statement of results Set ... DANGER.