Stored procedure failed: maximum open cursors exceeded

From: <Serguei.Goumeniouk_at_cibc.ca>
Date: 26 Jan 2006 06:12:37 -0800
Message-ID: <1138284757.400755.141300@o13g2000cwo.googlegroups.com>

   Dear Experts,
I have a problem with a simple stored procedure which is running under the Oracle 9.2.0.4.0
64 bit environment. After 100 - 200 calls, this stored procedure generates exception:

        ORA-01000: maximum open cursors exceeded

Really there are no cursors in this procedure (see the text below), but may be this exception is
a by-product of some other instructions. For the time being I just close/open the database
connection after every exception of this type. Could you please give me any idea why this
stored procedure is running out of cursor numbers and how I can fix this problem.
  Regards,
   Serguei.

FUNCTION fn_calcSettlementDate(

	exchCode	fill.fl_ex_code%TYPE, tradeCrcy fill.fl_trade_ccy%TYPE,
	secType		fill.fl_set_code%TYPE, seId fill.fl_se_id%TYPE, tradeDate
DATE
	) RETURN DATE
IS
	v_settleExchCode fill.fl_ex_code%TYPE;

BEGIN

	IF exchCode = 'ABCD' THEN
		SELECT se_ex_code INTO v_settleExchCode FROM security WHERE se_id =
seId;
	ELSE
		v_settleExchCode := exchCode;
	END IF;

	DECLARE
		v_sse_settle_date	DATE;
	BEGIN
		SELECT sse_settle_date INTO v_sse_settle_date
			FROM security_settle_exception WHERE sse_ex_code=v_settleExchCode
AND
				sse_se_id=seId AND sse_trade_date=tradeDate;
		RETURN v_sse_settle_date;

	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			NULL;
	END;

	DECLARE
		v_cse_settle_date	DATE;
	BEGIN
		SELECT cse_settle_date INTO v_cse_settle_date
			FROM calendar_settle_exception, exchange_calendar WHERE
				ec_ex_code=v_settleExchCode AND ec_ccy_iso_code=tradeCrcy AND
ec_set_code=secType AND
				cse_ec_id=ec_id AND cse_trade_date=tradeDate;
		RETURN v_cse_settle_date;

	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			NULL;
	END;

	DECLARE
		v_settle_date	DATE;
        v_day_of_week	NUMBER(1);
		v_ec_id			exchange_calendar.ec_id%TYPE;
		v_settle_days	NUMBER(2);
	BEGIN
		SELECT ec_id, ec_settle_period INTO v_ec_id, v_settle_days FROM
exchange_calendar WHERE
				ec_ex_code=v_settleExchCode AND ec_ccy_iso_code=tradeCrcy AND

ec_set_code=secType;

		v_settle_date := tradeDate;
		v_day_of_week := TO_CHAR( v_settle_date, 'D' );

		IF v_day_of_week=1 THEN
			v_settle_days := v_settle_days - 1;
			v_day_of_week := 2;
			v_settle_date := v_settle_date + 1;
		ELSIF v_day_of_week=7 THEN
			v_settle_days := v_settle_days - 1;
			v_day_of_week := 2;
			v_settle_date := v_settle_date + 2;
		END IF;

		LOOP
			IF v_day_of_week!=1 AND v_day_of_week!=7 THEN
				IF v_settle_days <= 0 THEN
					BEGIN
						SELECT eh_ec_id INTO v_ec_id FROM exchange_holiday
							WHERE eh_ec_id=v_ec_id AND eh_date=v_settle_date;
					EXCEPTION
						WHEN NO_DATA_FOUND THEN
	            	    	RETURN v_settle_date;
					END;
				ELSE
					v_settle_days := v_settle_days - 1;
				END IF;
			END IF;

			v_settle_date := v_settle_date + 1;
			v_day_of_week := v_day_of_week mod 7 + 1;

        END LOOP;
	END;

END fn_calcSettlementDate; Received on Thu Jan 26 2006 - 08:12:37 CST