Re: Fetch out of sequence - server 7.3.2

From: Dave Wotton <Dave.Wotton_at_dwotton.nospam.clara.co.uk>
Date: 2000/05/18
Message-ID: <PAVU4.506$Vh1.47752@nnrp4.clara.net>#1/1

Kenneth C Stahl wrote in message <3923E170.6836C80B_at_Unforgettable.com>...
>Delphis Group Limited wrote:
>>
>> Hi
>> On running a specific report on a client site a -1002 fetch out of
>> sequence is generated.
>>
>> Does anyone have any experience of this?
>
>This occurs in two different scenarios:
>
>1. when an application attempts to perform a FETCH against a cursor that
>has not been opened.
>
>2. When an application attempts to perform a FETCH against a cursor that
>has returned 1403 from the last fetch. Keep in mind that 1403 (No data
>found) is not an error because it is a positive value instead of a
>negative one. It is not an error to retrieve the last row of a cursor -
>however, it is an error to perform additional fetches once 1403 has been
>returned.

or in a third situation when you attempt to retrieve a row from a FOR UPDATE cursor after you have issued a commit. This example is in the Oracle documentation:

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; This example should return a "Fetch out of sequence" when fetching the 11th row.

As Steve Haynes points out in the thread "Fetch across commit" (12/5/00), there is an open bug with Oracle 8.1.6 which erroneously does NOT return this error.

Dave.

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

Received on Thu May 18 2000 - 00:00:00 CDT