Python - Update Cursor - Oracle Database - ORA-00923

[ModernElectric]

Good day:

  I am working on a project that requires me to take data from a file GDB table and update a non-gis table in an Oracle database. I have multiple Update Cursor scripts that I use to update GDB FC(s) and table(s) and have no problems with that. When I build the script to update the Oracle database table, I keep getting DBMS error [ORA-00923: FROM keyword not found where expected]

I cannot figure out what is going on and how to fix this. The fields, to and from and path(s) are 100% correct. 

I've attached the update cursor python script. Is there something I am missing? I have 100% read/write permission on the Oracle database that is connected via an ODBC connection. 

Is it possible that the ObjectID in the GDB table is causing a conflict since it doesnt exist in the Oracle table?

def AttributeUpdate(source_table, in_field, update_fields, join_table, join_key, join_values):
   
    # Make sure there is matching number of join and update fields
    update_dict = {}
    if len(update_fields) == len(join_values):
        for i in range(len(update_fields)):
            update_dict[join_values[i]] = update_fields[i]
            
        for k,v in update_dict.iteritems():
            # Create Dictionary
            path_dict = {}
            srows = arcpy.SearchCursor(join_table)
            for srow in srows:
                keyrow = srow.getValue(join_key)
                valrow = srow.getValue(k)
                path_dict[keyrow] = valrow
            del srow, srows
            
            # Update Cursor
            urows = arcpy.UpdateCursor(source_table)
            for row in urows:
                upkey = row.getValue(in_field)
                if upkey in path_dict:
                    row.setValue(v, path_dict[upkey])
                    urows.updateRow(row)
                else:
                    pass  # skip nulls
                
            del row, urows
            print '\'%s\' field in "%s" updated successfully' %(v, p.basename(source_table))
        
    else:
        print 'ERROR:  Number of update fields and value fields does not match'
            
if __name__ == '__main__':

    DCSI = r'Database Connections\DCSI.sde\DCSI.MEWCO_TRANS_RATING'
    GDB = r'C:\Users\cwafstet\Documents\GIS WORKING FILES LOCAL\ELECTRIC\MEWCo ELECTRIC SYSTEM.gdb\MEWCO_TRANS_RATING'

    # Attribute Update
    DCSI_fields = ['XFMR BANK RATING']
    GDB_fields = ['XFMR_BANK_RATING']

    AttributeUpdate(DCSI, 'XFMR BANK ID', DCSI_fields, GDB, 'XFMR_BANK_ID', GDB_fields)

[Anonymous User]

ORA-00923 means there's something wrong with your ORACLE sql statement:

ORA-00923 occurs when you try to execute a SELECT or REVOKE statement without a FROM keyword in its correct form and place. If you are seeing this error, the keyword FROM is spelled incorrectly, misplaced, or altogether missing. In Oracle, the keyword FROM must follow the last selected item in a SELECT statement or in the case of a REVOKE statement, the privileges. If the FROM keyword is missing or otherwise incorrect, you will see ORA-00923.

Seeing that you dont have a sql statement there in your cursors is interesting... Oracle is a different beast and since the table is non GIS, (which shouldn't make a difference in accessing it through an arcgis connection (I assume you are?) and cursor), you could use a pyodbc sql type connection and cursor using the oracle python package to make the updates.

curious why you are using the old cursors as well.  Maybe the .da. versions will handle Oracle better?

[ModernElectric]

Thank you for the feedback. I will take a closer look at the newer cursor to see if that may get me better results. I looked at the pyodbc sql module and that might be possible as well. Yeah, I am accessing the oracle database thru a ArcGIS connection. I will look into the Oracle Python Package as well to see if that might work better compared to the update cursor. 

[ModernElectric]

Jeff: 

Wondering, based on the existing code that I have there with the GDB table path and the Oracle table path and the fields, could you assist me with the proper way to write the code for updating the Oracle table via the Update cursor? Would greatly appreciate it since I am struggling with this big time.

[BlakeTerhune]

Are you using ArcMap or ArcGIS Pro?

[ModernElectric]

I am using ArcMap. However, this script is being done outside of ArcMap/ArcPro in IDLE, stand-alone Python script that will eventually be combined into a series of Batch file(s).

[Anonymous User]

The direct SQL method is more explicit and would need an overhaul from what you have above. I like @VinceAngelo 's suggestion to use the ArcSDEExecute method and would follow that route if possible. 

I think you could get the old data into a dictionary, and then iterate over it looking them up in the new values dictionary if there is a return, plug them into the sql and update.  esri provided an Conditional update using a transaction  example that I would follow to do this.

arcsdesqlexecute.htm 

[VinceAngelo]

Using arcpy.SearchCursor and arcpy.UpdateCursor instead of arcpy.da.SearchCursor and arcpy.da.UpdateCursor is not helping your cause. "Old Cursors" are kludgy and mostly only present for reverse compatibility -- they should not be used for new code (and pre-10.1 code should really be rewritten to not use them).

Whenever I've needed to do large database UPDATEs, I've used UPDATE in SQL statements. The usual procedure is to load the change data into a temporary table via FeatureClassToFeatureClass or TableToTable (or even arcpy.da.InsertCursor), then create an arcpy.ArcSDESQLExecute cursor to submit the SQL commands.  My most recent trick was to add an INSERT TRIGGER on the staging table, and let it do the UPDATE on the target table as each INSERT committed. This of course assumes that the target table is not versioned, though this is sometimes possible with the use of versioned views.

- V