Join Oracle table

MelanieRosenberg2 — Thu, 22 Feb 2018 20:08:46 GMT

I am trying to join an Oracle table to a feature class but have not found success yet...

Here is my code:

# Import arcpy moduleimport arcpyfrom 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 selectionarcpy.MakeFeatureLayer_management(parent, parentView)#Select all features that match the codes from the code listfor code in codeList:    arcpy.SelectLayerByAttribute_management(parentView, "ADD_TO_SELECTION", "Code = '"+ code+"'")#Copy the selected featuresarcpy.CopyFeatures_management(parentView, child)#Make Query Layer of Oracle table since it does not contain an incrementing ID fieldarcpy.MakeQueryLayer_management("Database Connections/db.sde", tableView, "SELECT   \n         field1,\n         field2,\n         field3,\n         SUM (amount) total\n    FROM layer\nGROUP BY field1\n", "field1", "", "", "")
#Join the gdb layer to the Connection Fees Table from oracle DB 
arcpy.JoinField_management(child, "field1", tableView, "field2")

I get a generic 999999 error.. Any thoughts?

Re: Join Oracle table

KimOllivier — Tue, 27 Feb 2018 09:06:21 GMT

Can you have another go at formatting the code? It is unreadable.

My strategy for the joinfield tool is Don't. Especially if it is a large table.

You will have trouble with joins across databases, and if it is in the same database, use a database join.

Instead I extract the data I need using a dictionary of tuples of the required fields using a SearchCursor dictionary comprehension.

Then add some fields to the target, or a copy and populate using an UpdateCursor.

This is always faster, reliable and works across databases. It does not need an index because a dictionary has a hash table underneath the hood.

To allow for missing values use the get function with a default of None.

topic Join Oracle table in Python Snippets Questions

Join Oracle table

Re: Join Oracle table