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

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