Re: Max open cursors

Hi. The code looks OK, and there won't be any hidden cursor with an integer output parameter. The only concern I'd have is that you *may* skip statement closures if the execute throws an exception. I'd put the statement closure in your finally blocks.

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 :)
> >

--

PS: Folks: BEA WebLogic is in S.F., and now has some entry-level positions for
people who want to work with Java and E-Commerce infrastructure products. Send
resumes to joe_at_beasys.com
--------------------------------------------------------------------------------
                    The Weblogic Application Server from BEA
         JavaWorld Editor's Choice Award: Best Web Application Server
  Java Developer's Journal Editor's Choice Award: Best Web Application Server
     Crossroads A-List Award: Rapid Application Development Tools for Java
Intelligent Enterprise RealWare: Best Application Using a Component Architecture
               http://weblogic.beasys.com/press/awards/index.htm