0

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.

  • By what do you mean "a unique incrementing field"?? If you mean a rownum column, then your view does not meet the requirement for a rowid column, and is likely to malfunction chaotically. – Vince Feb 22 '18 at 4:41
  • @Vince, the table has a primary key field. It's just not called "ObjectID" so I need to specify to ArcGIS which field to use. – Stella Feb 22 '18 at 19:30
  • It has never been a requirement that the rowid column be named "objectid". It had been a requirement that the rowid be a 32-bit integer, or, in the case of Oracle, a NUMBER which mapped to 32-bit integer, but Query Layers just need a unique not-null column. In the distant past having an actual PRIMARY KEY would prevent table registration; it's been most of a decade since I last used Oracle, but that was fixed even then. – Vince Feb 23 '18 at 3:35
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)
1

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

  • Ah, after posting I was trying "MakeQueryLayer" with no success. I just tried "MakeQueryTable" like you suggested and unfortunately this tool throws the following error: 000055 : Cannot create a Query Table for this workspace. My query table is connecting to an Oracle database - I think this is causing the error – Stella Feb 22 '18 at 19:22
  • 1
    You need to have sufficient privileges to execute. Recommend you troubleshoot the error. As a workaround you could use the original table view tool then export the table to a file geodatabse, then do the join. – Ben S Nadler Feb 23 '18 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.