Re: Max open cursors

Sorry,
  beats me. Are the cursors that you see in the view yours? Look at the actual text of the cursor to see what statement is occurring. After the process fails, is the app still connected? Check the sys.v_$session view to see who's logged on to the db.
Disconnect your app, and check the session view again to verify. Then check the 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 stored procedures once. Check the views, before you begin, and then after.

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 - 00:00:00 CST