Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Executing stored procedures from VB enviroment

Re: Executing stored procedures from VB enviroment

From: Sylvie Bérubé <sberube_at_cam.org>
Date: 1997/02/04
Message-ID: <32F7CEE2.2D72@cam.org>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US