Re: Fetch across commit

Steve Haynes wrote in message ...
>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.
>>
>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;
> END LOOP;
>END;
Ah, fair enough, remember I hadn't seen the documentation you were referring to. This example has two significant differences to my example, namely the "FOR UPDATE" clause on the select cursor and that you are not updating the table you're selecting from - just inserting into another table. So we're discussing rather different problems.

I'd agree that this example should return an ORA-01002 "Fetch out of sequence" error condition. I've just tested it in 7.3.4 and it does there.

I suppose we could be charitable and assume that Oracle 8.1.6 is clever enough to detect that the FOR UPDATE clause in this trivial example is unnecessary and perhaps optimises it out. Or perhaps the error condition is only returned in 8.1.6 when you're trying to fetch a row from a "FOR UPDATE" cursor where the lock has been released if Oracle detects that the row has actually been changed by someone else (whereas 7.3.4 reports the error without checking whether the row has been changed or not). Both scenarios are unlikely. I suspect it's a bug.

Dave.

--
If you reply to this posting by email, remove the "nospam" from my email
address first.