Python - Update Cursor - Oracle Database - ORA-00923

ModernGIS

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

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