Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!usc!apple!sun-barr!cronkite!newstop!sunaus.oz!sirius!par
From: par@sirius.Aus.Sun.COM (Paul Riethmuller SE Brisbane)
Newsgroups: comp.databases
Subject: Re: Oracle CURSORS useless ?
Message-ID: <1991Jun26.033043.22948@sunaus.oz>
Date: 26 Jun 91 03:30:43 GMT
Sender: news@sunaus.oz
Reply-To: par@sirius.Aus.Sun.COM
Organization: Sun Microsystems
Lines: 34


nigel@cnw01.storesys.coles.oz.au (Nigel Harwood) writes:
> Okay, useless may be a bit strong, but not much.  At least if
> this is true at any rate.
> 
> ... deleted
> 
> On asking Oracle they said that you cannot do a commit in the middle
> of fetching ?  In other words in doing what I want I can only do one
> commit and that's right at the end.  How can I do that if I don't
> know how many records I might have in the table ?
> 
> I've got something horribly wrong, right ?
> This can't be real, can it ?

Well, it does pose a problem for the developer - but you can some consolation
in knowing that this limitation isn't just Oracle's.  Every commercial SQL
DBMS I've ever seen closes all open cursors [SQL/DS, DB2, Ingres ...] when
a commit is issued.

(Although their forms languages may behave differently)

In your case, you could drive the transaction from the ASCII update file
and just issue UPDATE commands.  BTW, doing multiple fetch,update,commit
operations is intensive.  The alternative would be to do ~100 fetch,update
operations, record your position in the cursor scan (and possibly the file
offset - for recovery purposes) then issue 1 COMMIT.

Good Luck

Paul Riethmuller
Sun Microsystems, Brisbane.

Disclaimer:  these are the personal opinions of the author.