Using ArcPy to join feature class to Oracle Spatial View?

Question

I am trying to join an Oracle table to a feature class but I keep getting the error "000339 : Input does not have OIDs."

The view has a unique incrementing field already, but I am unsure how to specify with Python which field is the key?

Here is my code:

# Import arcpy module
import arcpy
from arcpy import env

#Set Variables 
parent = "Database Connections\\db.sde\\db.DBO.layer"
child = "Database Connections\\db.sde\\db.DBO.layer2"
codeList = ['SC21', 'SC22', 'SC23', 'SC24', 'SC25', 'SC26', '2C27', 'SC28', 'SC29', 'SC30', 'SC31', 'SC33', 'SC34', 'SC35', 'SC36', 'SC37']
parentView = "layer"
tableView = "connectionFees"
table = "Database Connections/oracle.sde/oracleDB.oracleTable"

if arcpy.Exists(child):
    arcpy.Delete_management(child, "FeatureClass")

#Make a feature layer in order to perform a selection
arcpy.MakeFeatureLayer_management(parent, parentView)

#Select all features that match the codes from the code list
for code in codeList:
    arcpy.SelectLayerByAttribute_management(parentView, "ADD_TO_SELECTION", "Code = '"+ code+"'")

#Copy the selected features
arcpy.CopyFeatures_management(parentView, child)

#Make table view of Oracle table since it does not contain an incrementing ID field
arcpy.MakeTableView_management(table, tableView)

#Join the gdb layer to the Connection Fees Table from oracle DB
arcpy.JoinField_management(child, "field1", tableView, "field2")

UPDATE:

I have tried to swap the line

arcpy.MakeTableView_management(table, tableView)

For

arcpy.MakeQueryLayer_management("Database Connections/db.sde", tableName, "SELECT   \n         field1,\n         field2,\n         field3,\n         SUM (amount) total\n    FROM layer\nGROUP BY field1\n", "field1", "", "", "")

While this is successful, when I run

arcpy.JoinField_management(child, "field1", tableName, "field2")

I get a generic 999999 error.. Any thoughts?

UPDATE #2:

Turns out there is a bug in ArcMap. When I make the Query Layer, ArcMap automatically adds a percent sign to the beginning of the layer name. This is preventing the Join, causing the 999999 error. My best workaround is to copy the features into the goedatabase and then perform the join. I will post a more detailed answer below.

Answer 1

I've had this issue with an Oracle table interacting oddly (same error about not having recognized OID field) with SDE features and tables, even though said tables of course had defined primary keys in the DBMS.

The quick and easy fix was to use the Register with Geodatabase function to define the OID field. I did have issues with this initially, as without the second parameter (your defined primary key), the tool actually creates a new unique ID field that it uses as OID. However, if you assign it like this it should work for you:

arcpy.RegisterWithGeodatabase_management(table, oid_field)

Answer 2

You will need to use the make query table tool instead of table view per the documentation