Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!swrinde!zaphod.mps.ohio-state.edu!cis.ohio-state.edu!ucbvax!hplabs!pyramid!infmx!aland
From: aland@informix.com (Colonel Panic)
Newsgroups: comp.databases
Subject: Re: Oracle CURSORS useless ?
Summary: cursors standards...  and extensions
Message-ID: <1991Jun29.033528.18434@informix.com>
Date: 29 Jun 91 03:35:28 GMT
References: <1991Jun26.033043.22948@sunaus.oz>
Sender: news@informix.com (Usenet News)
Organization: International Brotherhood of Geeks, Local 619
Lines: 40

In article <1991Jun26.033043.22948@sunaus.oz> par@sirius.Aus.Sun.COM writes:
>
>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 ?
>> 
>> 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.

This is an ANSI requirement; terminating a transaction (COMMIT or
ROLLBACK) releases all locks and closes all cursors.

Informix (at least) provides an optional cursor extension known as
a persistent cursor or "hold" cursor.  A cursor declared WITH HOLD,
once opened, remains open (regardless of transactions) until explicitly
closed (or until the database itself is closed).  Commit/Rollback 
still releases all locks, so the developer must be careful in her/his
integrity assumptions (any rows updated in the hold cursor will not
necessarily remain locked, even under explicitly higher Isolation
Levels).
 
>Paul Riethmuller
>Sun Microsystems, Brisbane.

--
Alan Denney  #  aland@informix.com  #  {pyramid|uunet}!infmx!aland
  We'll sue your drummer       in perfect four/four time
  We'll sue your manager       for his last thin dime
  We'll sue your record label, hey now ain't this fun
  We'll sue your best friend   if you still have one
  It ain't no crime,    'cause I'm     a rock and roll lawyer
                    -- The Austin Lounge Lizards