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

"Manfred Pruntsch" <manfred.pruntsch_at_ifcos.com> wrote in message news:b07nnr$lmnnj$1_at_ID-51546.news.dfncis.de...
> Hello PJ,
>
> > Assuming that you don't want to do anything when your getInstanceId
> function
> > returns a -1 value
> Correct. The dbms output is only for debugging...
>
> > , 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.
> What is the purpose of 'null' in the then branch?
>
> Unfortunately, I can it first check on monday (afternoon) because I have
no
> database at home...
>
> Thanks and regards,
> Manfred
>

Manfred -

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.

The NULL statement is an executable statement designed to explicitly specify no action -
it simply passes control to the next statement. You need to have a statement that can be
parsed and executed by the compiler in each clause of your IF statement. The NULL
[Quoted] statement does exactly what you need - it executes no specific action, and tells the compiler
that this option in your IF statement should not take any action. You need something like
this in your clause with the DBMS_OUTPUT statement, as that statement alone is not valid
(obviously) :-)

PJ Received on Tue Jan 21 2003 - 18:04:23 CET