Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.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: <25797@oolong.la.locus.com>
Date: 27 Jun 91 12:53:52 GMT
References: <1991Jun26.033043.22948@sunaus.oz> <1991Jun26.100717.17267@lut.ac.uk>
Organization: Locus Computing Corp, Los Angeles
Lines: 49

In article <1991Jun26.100717.17267@lut.ac.uk> elaps@lut.ac.uk (Alan Schwarzenberger) writes:
>I had a long running discussion with the Oracle help desk in the uk
>about this a few months back. A commit definitely closes the cursor.
>However, in version 6 of oracle, if you make a change to a column that
>is in your cursor, you'll see it immediately. For example
>If test_table has columns a, b, reject.
>
>EXEC SQL DECLARE C1 CURSOR FOR
>	select a, b from test_table for update of reject;
>EXEC SQL OPEN C1;
>do {
>	EXEC SQL FETCH C1 INTO :a, :b;
>	/* other code */
>	if (condition) 
>		EXEC SQL UPDATE test_table SET reject=1 WHERE CURRENT OF C1;
>} while (more_rows);
>EXEC SQL CLOSE C1;
>EXEC SQL COMMIT;
>
>In this example, changes you make to the column reject are visible in
>the cursor C1 th next time you go round the do{}while loop. I use this
>because I need to know what other rows of data have already been rejected
>when I llok at the current row of data.

 Be careful about generalizing this theory.  It will only work when 
 Oracle traverses the table through the cursor on a row-by-row basis
 as each FETCH is processed.  This will in fact occur quite often, 
 particularly when you have no ORDER BYs and no aggregates or GROUP BY
 expressions.  In addition, evem when you have ORDER BYs, this may still
 happen because either an index is available on the ORDER BY column(s)
 and/or Oracle chooses to build and sort a list of ORDER BY columns only
 and then retrieve by ROWIDs that it keeps with the sorted list.  
 
 This is fraught with peril, however, since you can not be sure this
 will always work.  If an ORDER BY on an unindexed column is used and
 the entire SELECT list is retrieved, sorted and held as an answer
 set, the result will be completely different and any updates or deletes
 you do while the cursor is open will no work.  In fact, you may block
 due to locks held on your rows (depending on the kind of cursor SELECT
 you are doing) and you might even find your update being cancelled for
 deadlock reasons.  In addition, you should realize that such behavior
 is totally inconsistent with any definition of the relational model
 since the cursor/FETCH mechanism is an artifact of procedural language
 requirements... In relational data bases and SQL, the SELECT should
 retrieve a complete set of rows at the instant in time when it is
 executed.  Any subsequent changes to those rows (if any are allowed)
 should not be reflected until a reSELECT is done.
 
 Jon Rosen