Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!swrinde!elroy.jpl.nasa.gov!decwrl!mejac!orchard.la.locus.com!prodnet.la.locus.com!jfr
From: jfr@locus.com (Jon Rosen)
Newsgroups: comp.databases
Subject: Re: Oracle CURSORS useless ?
Message-ID: <25762@oolong.la.locus.com>
Date: 26 Jun 91 18:45:45 GMT
References: <1991Jun26.033043.22948@sunaus.oz>
Organization: Locus Computing Corp, Los Angeles
Lines: 46

In article <1991Jun26.033043.22948@sunaus.oz> par@sirius.Aus.Sun.COM writes:
>nigel@cnw01.storesys.coles.oz.au (Nigel Harwood) writes:
>> ... 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.
 
  Tis true, tis true... If the SELECT is only ordered on an index
  and you are not doing anything else that requires prefetching the
  entire answer set (like aggregate selection (HAVING) or something),
  most DBMSs will not actually fetch the entire answer set but will
  in fact retrieve it row-by-row for you.  In this case, after each
  commit of the update (if you need to do this), you can issue a new
  SELECT cursor request with a WHERE predicate that asks for the next
  sorted column(s) of the index greater than the one just updated.
  This actually works pretty well in DB2, particularly if you are
  using static SQL (dynamic SQL sucks on this problem for the most
  part since it has to reoptimize the query each time you open the
  cursor).  DB2 Release 2.3 has actualy sort of fixed this with a
  COMMIT HOLD option which will commit but keep the cursor position
  available.  However, I have no idea yet what kind of locks will
  be held (the purpose of COMMIT afterall is also to release locks
  and if this doesn't happen, the cursor approach will still suck).
  What happens depends on the DBMS and your mileage may vary.

>(Although their forms languages may behave differently)
 
  If a DBMS's forms language (or for that matter any tool) behaves
  differently, than it is using something other than SQL as its
  access method (or at least is augmenting its SQL with some kind
  of under-the-covers scheme for locking and navigation).  This is
  not only not good but it can be very dangerous, particularly if
  you mix applications that use the tool with regular SQL applications
  and the DBMS doesn't integrate the usage properly.  Oracle was 
  certainly guilty of this in early SQL*Forms... I have no idea it
  that is still true.  It was supposed to have changed with Forms 4.0
  but I am not sure whether that has ever been released.

  Jon Rosen