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

[Quoted] "Manfred Pruntsch" <manfred.pruntsch_at_ifcos.com> wrote in message news:b06tpm$m1l6b$1_at_ID-51546.news.dfncis.de...
> Hi all,
>
> 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
> EXIT WHEN csr_reac%notfound;
> 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.
> The mystery is not the first occurance lead to the error. Five or six
times
> the instance_id is minus one and it works. But the seventh times for
> example, the error happens.
>
> My first aid was: I made sure that the instance id never got the -1 value
> back.
> And all was fine. It works perfectly. Unfortunately, it can be that I have
> no instance id than I must give back -1 ... and I have the same problem.
> Does anybody know what's going wrong?
>
> The error message is (Oracle online doc): ORA-01002 fetch out of sequence
> Cause: In a host language program, a FETCH call was issued out of
sequence.
> A successful parse-and-execute call must be issued before a fetch. This
can
> occur if an attempt was made to FETCH from an active set after all records
> have been fetched. This may be caused by fetching from a SELECT FOR UPDATE
> cursor after a commit. A PL/SQL cursor loop implicitly does fetches and
may
> also cause this error.
> Action: Parse and execute a SQL statement before attempting to fetch the
> data.
>
> Many thanks in advance for any hints.
>
> regards,
> Manfred
>

Manfred -

[Quoted] [Quoted] Assuming that you don't want to do anything when your getInstanceId function [Quoted] [Quoted] returns a -1 value, either change your code to read:

       if (instanceid_reac <> -1) then
          -- all Ok: process the result
          INSERT INTO temp_inst_relship values (id, r_entry);
       end if;

or
       if (instanceid_reac = -1) then
          -- id is missing
          DBMS_OUTPUT.PUT_LINE('data not found: '||r_entry);
            null;
       else
          -- all Ok: process the result
          INSERT INTO temp_inst_relship values (id, r_entry);
       end if;

I think the first would be a better approach, but use what meets your needs.

PJ Received on Thu Jan 16 2003 - 21:20:00 CET