PL/SQL: weird 'fetch out of sequence' error

[Quoted] I faced sometimes with a 'fetch out of sequence' (ora-01002) error, code below.
The responsible row for the error is the FETCH csr_reac .... row.

LOOP
...

    OPEN csr_reac;
    LOOP

      FETCH csr_reac INTO r_entry;                    -- this row caused the
error
[Quoted]       EXIT WHEN csr_reac%notfound;
[Quoted]       instanceid_reac := getInstanceId(reac_id, r_entry);
      if (instanceid_reac = -1) then
         -- id is missing
         DBMS_OUTPUT.PUT_LINE('data not found: '||r_entry);
      else
         -- all Ok: process the result
         INSERT INTO temp_inst_relship values (id, r_entry);
      end if;

    END LOOP;
    CLOSE csr_reac;
...

END LOOP;
...

The error occurs only when the function getInstanceId brings -1 value back. [Quoted] [Quoted] The mystery is not the first occurance lead to the error. Five or six times [Quoted] the instance_id is minus one and it works. But the seventh times for [Quoted] example, the error happens.

My first aid was: I made sure that the instance id never got the -1 value back.
[Quoted] [Quoted] And all was fine. It works perfectly. Unfortunately, it can be that I have [Quoted] [Quoted] no instance id than I must give back -1 ... and I have the same problem. Does anybody know what's going wrong?

[Quoted] The error message is (Oracle online doc): ORA-01002 fetch out of sequence [Quoted] Cause: In a host language program, a FETCH call was issued out of sequence. [Quoted] [Quoted] A successful parse-and-execute call must be issued before a fetch. This can [Quoted] occur if an attempt was made to FETCH from an active set after all records [Quoted] [Quoted] have been fetched. This may be caused by fetching from a SELECT FOR UPDATE [Quoted] cursor after a commit. A PL/SQL cursor loop implicitly does fetches and may also cause this error.
[Quoted] [Quoted] Action: Parse and execute a SQL statement before attempting to fetch the data.

[Quoted] [Quoted] Many thanks in advance for any hints.

regards,
Manfred Received on Thu Jan 16 2003 - 19:26:15 CET