Re: Max open cursors
Date: Mon, 6 Mar 2000 14:07:48 -0600
Message-ID: <SeUw4.950$8t6.5864_at_news.uswest.net>
[Quoted] [Quoted] OK, I'm REALLY starting to second guess myself on this, but if that's what [Quoted] it takes to solve the problem, so be it...
Here is the code from the SP called from Java method addPriceMaster(). I [Quoted] know there is an implicit cursor opened for the first select and the call to [Quoted] getNextTableID() opens another to get the primary key to return and then [Quoted] 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 [Quoted] to insert into child tables and each of those stored procedures first looks [Quoted] up a code to get the reference primary key ID to insert. To clarify, I first [Quoted] look up a primary key in a domain table and then insert into a different table.
[Quoted] [Quoted] I fully realize there are a few implicit cursors being opened here, but like [Quoted] 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 [Quoted] 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);
- 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;
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 :)
>> >
>