Re: Max open cursors

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

cheers,
cindy

[Quoted] 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 - 19:25:17 CET