Re: Max open cursors

OK, I'm REALLY starting to second guess myself on this, but if that's what it takes to solve the problem, so be it...

Here is the code from the SP called from Java method addPriceMaster(). I know there is an implicit cursor opened for the first select and the call to getNextTableID() opens another to get the primary key to return and then updates the table I get the primary key from. (Circumventing oracle SEQUENCES - don't ask ;-) )

Also, the addPriceMasterDetail() Java method calls 4 other stored procedures to insert into child tables and each of those stored procedures first looks up a code to get the reference primary key ID to insert. To clarify, I first look up a primary key in a domain table and then insert into a different table.

I fully realize there are a few implicit cursors being opened here, but like I said before, I'm COMMITing the transaction in my Java code and this doesn't help. Sooooo... how do I close an implicit cursor?

Even better, how do I keep track of how many cursors are currently opened in my database?

I hope I'm giving enough information to get the help I need.

Thanks,

Chad

• procedure to insert a row in the AAR_PRICE_MASTER_LIST table PROCEDURE add_AAR_PRICE_MASTER_LIST(o_price_master_id OUT AAR_PRICE_MASTER_LIST.PRICE_MASTER_ID%TYPE, i_price_master_year AAR_PRICE_MASTER_LIST.PRICE_MASTER_YEAR%TYPE, i_price_master_month AAR_PRICE_MASTER_LIST.PRICE_MASTER_MONTH%TYPE) AS l_check_year AAR_PRICE_MASTER_LIST.PRICE_MASTER_YEAR%TYPE; l_check_month AAR_PRICE_MASTER_LIST.PRICE_MASTER_MONTH%TYPE; BEGIN
  • need to check if month and year will be a duplicate BEGIN SELECT price_master_month, price_master_year INTO l_check_month, l_check_year FROM aar_price_master_list WHERE price_master_month = i_price_master_month AND price_master_year = i_price_master_year;
    • we really want it NOT to be found EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END;
  • if the check month and year are found, then we have to raise an exception IF l_check_month IS NOT NULL AND l_check_year IS NOT NULL THEN RomsErrorPackage.RaiseRomsError(RomsErrorPackage.ERROR_20307); END IF;
  • get the next price master id ROMS_Utility_package.getNextTableId('AAR_PRICE_MASTER_LIST', o_price_master_id);

    IF o_price_master_id < 0 THEN

        RomsErrorPackage.RaiseRomsError(RomsErrorPackage.ERROR_20108);  END IF;

• now do the insert INSERT INTO AAR_PRICE_MASTER_LIST (PRICE_MASTER_ID, PRICE_MASTER_YEAR, PRICE_MASTER_MONTH) VALUES (o_price_master_id, i_price_master_year, i_price_master_month); END; C. Ferguson wrote in message <38C3F80A.B2E3A6DE_at_rationalconcepts.com>...
  >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 :)
  >> >
  >