Re: ODBC and Open Cursors
Date: Sat, 18 Feb 1995 03:26:49 GMT
Message-ID: <shayD46Ewp.216_at_netcom.com>
sarek_at_cais3.cais.com (Scott T Johnson) writes:
>Hi all,
> I working on a project using Visual Basic Prof and ODBC to attach to an
>Oracle database. I have written code to generate several queries to check
>for valid data and then insert the data into the database. All of this is
>using ODBC calls:
>
> Set ds = dB.CreateDynaset(SQLQuery, DB_SQLPassThrough)
> DoEvents
>where SQLQuery is a select or update. This code is in a loop
>and may be executed from 1 to 300 times and passing differents SQL
>statements.
>The problem: After ~100 Inserts, ODBC returns the error:
>ORA-6512, ORA-1000 max cursor exceeded. I have raised to open cursors in
>Oracle to 200 but still have the same problem. I have even sent a commit
>to the database after every 50 records. Is it possible to close the
>cursor after a call like the one above?
This is a bug in the MS JET layer. MS has written a white paper on this, but I'm not sure of the doc number. Basically, you lose a cursor for every dynaset you open and close. There is know good workaround. MS recommends not to use dynasets heavily against Oracle, and to set opencursors as high as possible.
Have you looked into Oracle Objects for OLE? At least you won't have to wait on MS for fixes to their JET layer....
Good Luck,
Christian
-- --- Christian Shay shay_at_netcom.com <A href=ftp://ftp.netcom.com/pub/sh/shay/html/shay.html> Click here </A>Received on Sat Feb 18 1995 - 04:26:49 CET