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

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 [Quoted] database at home...

Thanks and regards,
Manfred

"PJ Pugh" <msee90+2_at_yahoo.com> schrieb im Newsbeitrag news:3e2713fd_at_rpc1284.daytonoh.ncr.com...
> "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 -
>
> Assuming that you don't want to do anything when your getInstanceId
function
> 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 Fri Jan 17 2003 - 02:49:01 CET