Re: Max open cursors

And I forgot,
  unless you set autocommit to false after you opened up the connection, I believe your commits are unnecessary.

cindy

"C. Ferguson" wrote:

> sys.v_$open_cursor...
>
> you don't close the implicit cursor, as far as I know.
>
> so, look at the sys.v_$open_cursor view in sqlplus during your app's runs.
>
> good luck,
> cindy
>
> Chad Sheley wrote:
>
> > 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 :)
> > >> >
> > >
Received on Mon Mar 06 2000 - 00:00:00 CST