Re: Max open cursors

Hi,
  from your code, it looks ok. Now, what about the stored procedures? Are they coded cleanly, ie. closing whatever it opens? If, for some reason, you can't check your stored procedure (bad situation), then close your connection and reopen it after the statement close. This is meant to be a test case only, and should not be considered the norm. If this points to a somewhat constant number of curors open, then you really need to dig into the stored procedures.

cheers,
cindy

Chad Sheley wrote:

> 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 - 12:25:17 CST