Re: Max open cursors

From: Chad Sheley <csheley_at_usa.capgemini.com>
Date: Mon, 6 Mar 2000 09:52:14 -0600
Message-ID: <bvQw4.914$8t6.2740@news.uswest.net>

Thanks for the responses, but I'm certain I'm closing the statements, but I'm curious about the closing the resultset issue.

The SP I'm calling DOES have one output parameter, but it's an integer, so what is there to close for that? Is there an implicit resultset I don't know about?

I am including the code below so I can show what's going on. The call to "addPriceMasterDetail" looks pretty much the same (rest assured of the statements being closed) except for no commit() and no output parameters.

public void addPriceMaster(PriceMasterTransaction transaction) throws FatalDBException, NonFatalDBException {  CallableStatement theStatement = null;

 if ( transaction.getPriceMaster() == null ) {   Object [] msgArgs = { this.getClass().getName()};   transaction.getErrors().addElement( RomsMessage.buildMessage( msgArgs , RomsResources.INVALID_TRANSACTION));
  throw new NonFatalDBException("PriceMasterDataAccess.addPriceMaster: Null major component repairs vector.");
 }
 try {
  theStatement = this.getDatabaseConnection().prepareCall("{ call RomsAarPackage.add_AAR_PRICE_MASTER_LIST(?, ?, ?)}");

  theStatement.registerOutParameter(1, Types.INTEGER);
// Set all the input parameters that will be inserted into the table.
  this.setStatementValue(theStatement, 2, transaction.getPriceMaster().getEffectiveYear());   this.setStatementValue(theStatement, 3, transaction.getPriceMaster().getEffectiveMonth());

  theStatement.execute();
// get the newly added price master id
  transaction.getPriceMaster().setKey(new Long(theStatement.getInt(1)));   theStatement.close();

// Add price master detail

  addPriceMasterDetail(transaction);

// Commit the data

  this.getDatabaseConnection().commit();  } catch (SQLException sqle) {
// rollback

  try {
   this.getDatabaseConnection().rollback();   } catch (SQLException s) {}

  ErrorManager.log(sqle,"PriceMasterDataAccess.addPriceMaster");

  if (sqle.getErrorCode() == PriceMasterDataAccess.PM_ALREADY_EXISTS) {    Object [] msgArgs = {new Integer(sqle.getErrorCode())};    transaction.getErrors().addElement( RomsMessage.buildMessage( msgArgs , RomsResources.PM_ALREADY_EXISTS));

   throw new NonFatalDBException("Non-Fatal Database error adding price master.", sqle);
  } else

   Object [] msgArgs = {new Integer(sqle.getErrorCode())};    transaction.getErrors().addElement( RomsMessage.buildMessage( msgArgs , RomsResources.DATABASE_ERROR));

   throw new FatalDBException("Fatal Database error adding price master." , sqle);
  }
 } finally {
   ;
 }
}
Aznable wrote in message <38C27FC1.326FC8CC_at_blunet.it>... >> I'm wondering how far I can increase the OPEN_CURSORS parameter on my Oracle

>> 8.05 database until some different problem comes up besides "Max Open
>> Cursors Exceeded"?
>>
>> I have a Java class that is reading some records from a file, doing some
>> validation and then calling a series of stored procedures to insert the

data

>> into 4 tables.
>
>You are not closing cursors on your java classes.
>TO close the cursors you have 2 close the
>satement you have create and the result set also.
>We had a similar problem with java.
>I cant remember exactly which methods
>you have 2 use becos i am at home and i havent
>the documentation right now.
>If you need more infoz feel free 2 send me a mail
>
>> I should also mention that we've added COMMITs in the Java code at

various
>> places to try and circumvent the problem, to no avail. Am I missing >> something here too? I thought a COMMIT would release the cursor resources.

>
>Nah...U have 2 close the statement and the resultset :)
>
>> Is there anything else to try for this situation, or should I just keepin
>> incrementing OPEN_CURSORS?
>
>No! Every open cursors takes resources :)
>
>Ciao :)
>

Received on Mon Mar 06 2000 - 09:52:14 CST