Re: Max open cursors

[Quoted] Yes, I have autocommit set to FALSE.

[Quoted] I checked the v$open_cursor view and there are 14111 cursors!!! How can this [Quoted] be?!! I have the OPEN_CURSORS parameter set to 5000?

[Quoted] Also, how do I clean these up? After the guilty process failed, these [Quoted] 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 - 17:47:28 CET