Re: Max open cursors

Sorry,
[Quoted]   beats me. Are the cursors that you see in the view yours? Look at the actual [Quoted] text of the cursor to see what statement is occurring. After the process [Quoted] fails, is the app still connected? Check the sys.v_$session view to see who's logged on to the db.
[Quoted] Disconnect your app, and check the session view again to verify. Then check the [Quoted] cursor view (user_name and sql_text) to see what's what...

Write a tighter test case where iteration occurs through your app and all the [Quoted] stored procedures once. Check the views, before you begin, and then after.

[Quoted] Sorry I don't have the answers you are looking for. regards,
cindy

Chad Sheley wrote:

> Yes, I have autocommit set to FALSE.
>
> I checked the v$open_cursor view and there are 14111 cursors!!! How can this
> be?!! I have the OPEN_CURSORS parameter set to 5000?
>
> Also, how do I clean these up? After the guilty process failed, these
> cursors still show up in the view.
>
> Chad
>
> C. Ferguson wrote in message <38C43D73.36B05BEB_at_rationalconcepts.com>...
> >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()};
> >> > >>
> ansaction.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())};
> >> > >>
> ansaction.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())};
> >> > >>
> ansaction.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 Tue Mar 07 2000 - 19:04:49 CET