Re: ORA-01002 "fetch out of sequence" error with ORAPERL

D461-David_F_Haertig(Dave)83040 wrote:
>
> Does anyone know what a "fetch out of sequence" (ORA-01002) error
> from Oracle means? The "oerr ora 1002" says it's caused by something
> like trying to fetch from a closed cursor - or something similar. It
> also says something about a PL/SQL "cursor loop" possibly causing
> this error. I don't think we're doing any of these things. Here's
> what we're doing when the error occurs.
>
> This is an ORAPERL application (version 4 of PERL, version 7.1.4 of Oracle,
> NCR hardware, UNIX System V). Here's generally how it works (in pseudo-code):
>
> Login to Oracle
> Open "outer" cursor (has a where clause, but no bind variables)
> Check for Oracle errors opening "outer" cursor
> Open "inner" cursor (has a where clause, uses bind variables)
> Check for Oracle errors opening "inner" cursor
> while (fetch from outer cursor) {
> Do various counting, summarizing, etc
> bind data from the outer fetch to the inner cursor variables
> Check for Oracle errors binding "inner" cursor
> while (fetch from inner cursor) {
> Do more counting, summarizing, etc
> }
> Check for Oracle errors fetching from "inner" cursor
> }
> Check for Oracle errors fetching from "outer" cursor <<<< FAILURE >>>>
> Close inner cursor
> Close outer cursor
> Logoff Oracle
> Print Results
> Exit
>
> We sometimes see "fetch out of sequence" errors (ORA-01002) when we
> check for fetch errors from the "outer" cursor. How far the program gets
> before a "fetch out of sequence" varies - sometimes it dies early,
> sometimes it runs for quite a while before dying, sometimes it runs
> to completion without error. We can't really put a finger on what
> causes the variance - things *seem* to run better at night, when there
> are less users hitting the database. But we can't say this is really
> the case with high certainty. While we fail more often during the day,
> we also fail at nights sometimes too.
>
> (snip)
>
> We have tried using open-fetch-close on the "inner" cursor instead
> of the suppossedly more efficient bind-fetch operation. This doesn't
> make much difference, except the program *seems* to get farther before
> a "fetch out of sequence" error occurs. Maybe it only coincidentally
> seems to get farther.
>
> (snip
>
>

Dave,

   The *only* reason I know for this error is, indeed, fetching from a cursor after closing it.

   I was confused by your statement that you tried the "less efficient" open-fetch-close inside the outer cusor loop. As far as I am aware, you *must* close the inner cursor, bind the new variables and re-open it before you can fetch using the new bind variables. Thus the sequence is:
for row in outer cursor -- implicit OPEN loop

   ... process...
   for row in inner cursor -- implicit OPEN    loop

      ...process...
   end loop; -- implicit CLOSE
   ... process...
end loop; -- implicit CLOSE

Are you, by any chance, using explicit OPEN and CLOSE statements within an
implicit loop structure of "for row in cursor loop ... " structure?

This can cause the problem, since the implicit loop structure contains its own open and close statements as above.

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"