Path: utzoo!utgpu!news-server.csri.toronto.edu!bonnie.concordia.ca!ccu.umanitoba.ca!herald.usask.ca!alberta!brazeau.ucs.ualberta.ca!unixg.ubc.ca!ubc-cs!uw-beaver!mit-eddie!mintaka!think.com!yale.edu!cmcl2!uupsi!dlogics!hermes!dlm
From: dlm@hermes.dlogics.com
Newsgroups: comp.databases
Subject: Re: Oracle CURSORS useless ?
Message-ID: <661.2869e33d@hermes.dlogics.com>
Date: 27 Jun 91 19:08:13 GMT
References: <1274@cnw01.storesys.coles.oz.au>
Organization: Datalogics, Inc., Chicago, IL
Lines: 19

nigel@cnw01.storesys.coles.oz.au (Nigel Harwood) writes:
> 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 ?

There is no law that says "Thou shalt commit after every update". In fact,
after you fetch a row, you can use UPDATE with the CURRENT OF CURSOR clause
specifically to make use of the cursor information at hand. When you run out
of rows in the result set, THEN you close the cursor, and THEN you commit.

Committing after each update is inadvisable for performance reasons; knowing 
the number of rows in the result set is not especially important. Oracle will
jump out of the FETCH loop when the result set is emptied. Refer to EXEC SQL
WHEN SQLERROR ... in the manual.
-- 
Dave Mausner, Sr Tech Consultant / Datalogics Inc / Chicago IL / 312-266-4450
                        dlm@hermes.dlogics.com
            "Don't talk about it -- just show me the code!"