Writing arcpy.ArcSDESQLExecute connection string for Oracle?

Question

When using arcpy.ArcSDESQLExecute, I was trying to debug why a sql statement was not working and throwing an error. I thought the sde connection file that I was passing might be the problem because I was attempting to access an Oracle database that was not Esri spatially enabled. The documentation had a couple examples of passing database identification strings to create the connection versus using the sde file, but it was unclear, in particular for the set of strings to pass for an Oracle connection. Does anyone have an example for Oracle?

(FYI, The errors passed back from arcpy.ArcSDESQLExecute can be cryptic. I don't know why the database error message doesn't come back. For example, "AttributeError: ArcSDESQLExecute: StreamPrepareSQL ArcSDE Error -37 \uf61c" )" seems to equate to "ORA-00942: table or view does not exist".)

• Vince: You can also just provide a connection file (.sde)

Answer 1

WARNING - DO NOT USE ON VERSIONED TABLES OR FEATURE CLASSES.

DO NOT USE ON ANY enterprise geodatabase SYSTEM TABLES.

On non-versioned tables and fcs, feel free to use ArcSDESQLExecute to update non-required columns (e.g. not -- objectid, shape, se_anno_cad, etc. Check field.required property after arcpy.ListFields); delete rows; get row counts; etc. For inserting, you need to call sde.gdb_util.next_rowid(), I prefer just using an arcpy.da.cursor as I've found it to be faster than calling next_rowid().

Normally, I would just use the sde connection file with ArcSDESQLExecute, but here's how to figure out the set of strings to pass. The ArcGIS documentation is not consistent even on the same version help page. Sometimes the second parameter is listed as port, but it seems to be the sde instance string.

# ArcGIS 10.4
#Oracle TNS Entry:
#GISDB=
# (DESCRIPTION=
# (address= (protocol=tcp)(host=myserver-name)(port=1521)
# )
# (CONNECT_DATA=
# (SERVICE_NAME=GISDB_SERVICE_NAME)
# )
# )
pathGDBSource = "C:/proj/census/[email protected]"
#normally, just use sde connection file to setup
egdb_conn = arcpy.ArcSDESQLExecute(pathGDBSource)
#alternately, setup with connection string: server, instance, None, user, pw
desc = arcpy.Describe(pathGDBSource)
cp = desc.connectionProperties
print cp.instance #prints sde:oracle$sde:oracle11g:GISDB - GISDB is TNS entry name
strInstance = cp.instance 
strServerName = "myserver-name" #get from TNS entry HOST
strUser = "USER"
strPass = "password"
egdb_conn = arcpy.ArcSDESQLExecute(strServerName,strInstance,"#",strUser,strPass)
#Now, get a record count
sqlGetCount = "SELECT COUNT(*) FROM {0} where {1}"
print "Census block count: {:,}".format(int(egdb_conn.execute(sqlGetCount.format("CENSUS_BLOCKS_FC","1=1"))))