Underlying DBMS error [ORA-01002: fetch out of sequence] -- consistently popping up

Question

I have a series of python scripts linked to tools in ArcToolbox.  (still running 10.0 at the moment, though plan to upgrade in the near future)  Without going into great detail, 2 or 3 of these scripts are regularly triggering an Error popup that says "Underlying DBMS error  [ORA-01002: fetch out of sequence]" and the tool is effectively paused until I click OK on it... sometimes take one click sometimes takes a dozen.

I searched online for any information about this and what I found talks about fetching from cursors where there are no more rows to fetch.  But these errors pop up even when I'm not running a cursor.  They sometimes occur in 'for' statements, and even something as ridiculously simplistic as adding a field to a table from a list of fields will cause it.  And what's worse is that it doesn't happen with any predictable regularity.  Running a script that creates 20 new fields in a table will not always cause the error to kick in for the same field every time.  And the cherry on top is that running these scripts inside an IDE does not trigger the error, so I don't believe the code is in error.  There's something screwed up in ArcMap yet again.

It's getting extremely frustrating having to click on error boxes for tools that should be standalones.  Am I the only one experiencing these issues or does anybody else have similar experiences that they've been able to troubleshoot successfully?

(I'll also note that these scripts run solely on data in SDE, if that matters)

DanMarrier · Answer

It's not just adding fields that causes it... that was just one example.  I'm running another script right now and the code that just triggered the same error again was the creation of a statistics table and it happened just after this line:arcpy.Statistics_analysis(x_view,mstr_tab,[["STR_NM_STD","FIRST"],["STR_NM_STD","LAST"],["COMMENTS","FIRST"]],"FULL_STR")

The variables and field names themselves are fairly irrelevant, but I would not expect the creation of a statistics table to cause an error associated with cursors.

I also find it hard to believe that the arcpy.AddField command exists yet is unsuitable for it's express purpose.  What bothers me the most though is, as I said before, these errors don't occur when running inside an IDE.  This only happens when running inside of ArcMap as a tool.

We're running on SP2.  Can't try it on 10.1 yet because our admin hasn't installed it due to other incompatibility problems.  It's not a new error... it has been happening for months and months, but it is so inconsistent that troubleshooting is difficult.  I could run the same script 10x in a row, and maybe the error pops up in the same spot 3 times.  Right now I'm just trying to see if anybody else has had the same problem and been able to pinpoint it's cause.

MathewCoyle · Answer

If think you may be conflating your Oracle cursors and arcpy cursors. Any access to a DB could trigger a cursor to fetch the data. It would be helpful to post your code even though it may not be directly in error. Also, adding fields in your DB changes the schema. This is generally not recommended practice to do on the fly, especially if it requires subsequent processing dependant on those created fields.

You mentioned you were running 10.0. What SP are you on? Have you tested to see if this behaviour occurs in 10.1? Is this a new error that occurs in previously working scripts or has it always occurred? Have you contacted your DBA to see if any tuning needs to be done or if the Oracle logs show any more info?