RuntimeError: Underlying DBMS error ORA-00936: missing expression schema.tableName STATE_ID is not column in table?

Question

I created a search cursor to retrieve some data from a feature class.

The broken example shown below is used successfully in other scripts I created. The only difference that comes to mind is I'm retrieving records from a database table, not a feature class, in the successful cases.

The broken code throws an error on the line, for row in inCursor:. The message is

RuntimeError: Underlying DBMS error [ORA-00936: missing expression
] [schema.tableName][STATE_ID = 718361]. `STATE_ID` is not a column in my table.

I prefer to explicitly send a list of fields to the cursor as there are 50+ columns in this table.

The working example shown below is a result of a post I found. I tried it to ensure my database connection was good. The code prints out row data as expected.

What is wrong with the broken code sample? Any ideas why it works with database tables but not feature classes? Which is the better way to write a cursor? I'm not opposed to going back to rework some code using the now-broken cursor.

This code works:

fields = [f.name for f in arcpy.ListFields(inFC)]   # get field list
features = list(cursor_to_dicts(arcpy.SearchCursor(inFC), fields))

for feature in features:
    print(feature)

This code DOES NOT work:

inputFields = [ "Field1", "Field2", "Field3", "Field4", "Field5", "Field6" ]

with arcpy.da.SearchCursor(inFC, inputFields) as inCursor:
    for row in inCursor:
     # do stuff

---

Update #1

The feature class is in an enterprise geodatabase (Oracle). We may be using versioning but I can't confirm at the moment.

Answer 1

The error message states:

STATE_ID is not a column in my table

So try this code snippet to show whether or not it does exist there:

import arcpy
inFc = <your feature class>
fields = arcpy.ListFields(inFC)
for field in fields:
    print field.name

You appear to be using geodatabase versioning so the above may or may not reveal anything. I think you should edit more details into your question about where this feature class is stored.

To learn about where this STATE_ID column comes from I recommend reviewing the Online Help for System tables of a geodatabase stored in Oracle because it is:

The identifier of the database state to which this version points

Answer 2

Many of the columns listed in inputFields did not actually exist in the database table. Documentation says they should exist but they don't. STATE_ID was not one of the missing columns and I'm still not sure what that was about. Now that I updated the column names in inputFields, everything is fine.