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_at_news.uswest.net>

[Quoted] [Quoted] Thanks for the responses, but I'm certain I'm closing the statements, but [Quoted] 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 [Quoted] [Quoted] what is there to close for that? Is there an implicit resultset I don't know about?

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

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

 if ( transaction.getPriceMaster() == null ) {   Object [] msgArgs = { this.getClass().getName()};   transaction.getErrors().addElement( RomsMessage.buildMessage( msgArgs , [Quoted] RomsResources.INVALID_TRANSACTION));
  throw new NonFatalDBException("PriceMasterDataAccess.addPriceMaster: Null major component repairs vector.");
 }
 try {
[Quoted] [Quoted]   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.
[Quoted]   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))); [Quoted]   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 , [Quoted] RomsResources.PM_ALREADY_EXISTS));

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

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

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

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

data

[Quoted] >> into 4 tables.
>
[Quoted] >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 [Quoted] >> 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
[Quoted] >> incrementing OPEN_CURSORS?
>
>No! Every open cursors takes resources :)
>
>Ciao :)
>

Received on Mon Mar 06 2000 - 16:52:14 CET