Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Executing stored procedures from VB enviroment
Wintec spa wrote:
>
> 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
Check the parameter OPEN_CURSORS of your instance. The default 50 is
way too low. Increase it at least to 250 then see if you have the same
problem.
Hope this helps.
Sylvie Bérubé
sberube_at_cam.org
Received on Tue Feb 04 1997 - 00:00:00 CST