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

 Hello Scott,

"Scott Mattes" <Scott_at_TheMattesFamily.ws> wrote in message news:PfDV9.85040$VA5.13256456_at_news1.news.adelphia.net...

> Since a search on tahiti.oracle.com shows no reference to getInstanceId,
we
> probably need to see what that function is doing to be able to tell.
The getInstanceId is a userdefined function. I wrote the code. This function is quite simple. It looks if an instance exist (select query) and return the id of the instance -1 otherwise (instance doesn't exist). The function is not the problem.

It works fine with many other procedures.

To get the procedure (the code with LOOP etc.) running I modified the code of the function to get never -1 back. That mean the else branch is used in every time. And it works. But I can't leave it in that way. The function must give -1 back if the instance does not exist.

> also, what version, etc, etc, etc
Oracle 9i, 9.2.x, SQL+, PL/SQL, normal user, but by the way I watched many open sessions in the managementconsole although only one user was logged in...

Thanks

Manfred

"Scott Mattes" <Scott_at_TheMattesFamily.ws> schrieb im Newsbeitrag news:PfDV9.85040$VA5.13256456_at_news1.news.adelphia.net...
> Since a search on tahiti.oracle.com shows no reference to getInstanceId,
we
> probably need to see what that function is doing to be able to tell.
>
> also, what version, etc, etc, etc
>
>
>
> "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
> >
> >
> >
> >
> >
> >
>
>
Received on Fri Jan 17 2003 - 02:28:04 CET