Re: Fetch across commit

In article <yGxR4.10126$OO6.1077319_at_nnrp4.clara.net>, Dave Wotton <Dave.Wotton_at_dwotton.nospam.clara.co.uk> writes
>
>Your rollback segments may well be large enough to avoid the "snapshot
>too old" problem". Remember that the "fetch across commit" example I
>gave (and probably the one in the documentation) only makes the
>the "snapshot too old" situation a possibility (albeit a likely one),
>it doesn't *guarantee* that it will occur.
>

Hi Dave,

Thanks for the reply, but I disagree.

The oracle example has a cursor for update and a loop. some code where "if cnt > 10 then commit" and states that it will fail after the tenth fetch - which is what I'd expect. The error is not snapshot too old, it's fetch out of sequence as I recall.
I've still got no response from support as yet.

The following is the documentation:

The FOR UPDATE clause acquires exclusive row locks. All rows are locked when you open the cursor, and they are unlocked when you commit your transaction. So, you cannot fetch from a FOR UPDATE cursor after a commit. If you do, PL/SQL raises an exception. In the following example, the cursor FOR loop fails after the tenth insert:

DECLARE
   CURSOR c1 IS SELECT ename FROM emp FOR UPDATE OF sal;    ctr NUMBER := 0;
BEGIN
   FOR emp_rec IN c1 LOOP -- FETCHes implicitly

      ...
      ctr := ctr + 1;
      INSERT INTO temp VALUES (ctr, 'still going');
      IF ctr >= 10 THEN
         COMMIT;  -- releases locks
      END IF;