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: L. Tseng <lesliet_at_u.washington.edu>
Date: 1997/01/29
Message-ID: <5co1vn$icj@nntp1.u.washington.edu>#1/1

How do you run the SP x times? Manually or looping from the VB apps? Did you check v$open_cursor table when that happened? Since the error message indicating open cursor problem, I suspect the cursor did not get closed as you would expect.

In article <32EF339D.2362_at_iol.it>, Wintec spa <wintec.pd_at_iol.it> 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
Received on Wed Jan 29 1997 - 00:00:00 CST

Original text of this message

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