Re: Executing stored procedures from VB enviroment

Consider also close_cached_open_cursors=TRUE (default is FALSE).

Marek

Sylvie Bérubé wrote:
>
> 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 Sat Mar 01 1997 - 00:00:00 CST