Executing stored procedures from VB enviroment

From: Wintec spa <wintec.pd_at_iol.it>
Date: 1997/01/29
Message-ID: <32EF339D.2362@iol.it>#1/1

Hello,

I have developed an application in this environment.

	Novell Netware
	Oracle Server 7.1
	SQL*Net 1.x
	Visual Basic 3.0
	Q+E database library 2.0
	ODBC

This applications works properly since 3 years, but every new year I have the same problem.
I use some stored procedures to delete old data (3 years on line). Every sp is launched many times while different types of date have to be checked and then deleted.
After the same sp is launched "x" times, everything stops because of "ORA-01000 Maximum number of cursors open". During these running there aren't other programs on-line. This is an example of these simple stored.

CURSOR RA IS SELECT ... FROM ... WHERE ... AND ... FOR UPDATE OF ...; BEGIN

	OPEN RA;
	FETCH RA INTO ...;
	LOOP
		EXIT WHEN RA%NOTFOUND;
		INSERT INTO .. VALUES (...);
		DELETE FROM ... WHERE CURRENT OF RA;
		FETCH RA INTO ...;
	END LOOP; 
	CLOSE RA;

END; The number of "x" times is always arround 40 (38 most times).

I have done many tests, changing the sequence of call, increasing the number of max_opened_cursor and so on.
But the problem is still there...

Every kind of suggestion will be appreciated and replyed.

Thank you.

Gabriella Berto Received on Wed Jan 29 1997 - 00:00:00 CST