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

Here's a couple of possibilites you might like to consider, although it's difficult to say for sure without seeing your code:

First of all, I'm a bit worried about your error handling. I know that you've only given pseudo-code here, and the code itself may be correct, but the error handling for a fetch should look something like this:

FETCH
Check for errors
While (no errors and data returned)
{

    process rows

    FETCH
    Check for errors
}

This applies to both the inner and the outer loop. If you don't have a mechanism like this in place, you will quite possibly be getting the error reported against the wrong statement (i.e. your error is actually somewhere else.)

Once you've done this, try putting in counter variables for each of the loops, so that you can actually see numbers for how far it's getting (rather than saying things like it *seems* to run better at night). My money says that once you do this you'll find that it dies in exactly the same place each time, possible on the first time through the inner loop, the second time through the outer loop. If it's falling over in a different place each time, there's some very strange stuff going on, and it may be best to speak to Oracle tech support.

One last thing to consider - I've had problems in the past nesting SQL commands (including cursors) inside cursors in Pro*C. Although I've never used ORAPERL, I suspect that there are similar pitfalls there. The problems come when using structures such as sqlca. I was doing something like this (very simplified):

loop
{

    if (sqlca.sqlcode != 1403)
    {

        FETCH         do processing

        INSERT
    }
}

The problem is that by the time the if statement executes during the second time round the loop, sqlca contains data from the INSERT statement, not from the FETCH - therefore, it doesn't tell me if there is more data, which is what I want to know.

The answer is that after every SQL statement, copy anything from sqlca which you're going to need into a local variable, and then use the variable from then on. This problem can catch you out in all kinds of ways, and I've taken to putting sqlca values into local variables almost all the time now.

Hope that something in here helps.

Dean

In article <EH90H9.4Eo_at_drnews.dr.lucent.com>,   haertig_at_lucent.com () 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.
>
> The "outer" cursor is working thru a table of about 2 million rows. The
> "inner" cursor is hitting an even larger table - about 12 million rows.
> The where clauses we use on both "inner" and "outer" cursors allow us
> to use indices instead of full table scans. We do have a plan to
> divide up our tables to get them down to manageable size. But for
> now, we're stuck with these large and inefficient tables.
>
> 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.
>
> We've also tried making "fetch out of sequence" non-fatal. We continued
> to try fetching after this error. Didn't work. Once you see this
> error, all subsequent fetches exhibit the same error. As expected,
> but we were getting pretty desperate to try this approach.
>
> This is kindof an urgent problem (to say the least!) for our database.
> Any help or pointers would be greatly appreciated!
>
> --
> Dave Haertig
> haertig_at_lucent.com
> --
> Dave Haertig
> haertig_at_lucent.com

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet