"AttributeError: ArcSDESQLExecute: StreamExecute ArcSDE Extended error 900 ORA-00900: invalid SQL statement" error when running SQL query via ArcPy

Question

I am trying to run a SQL query in ArcGIS Pro ArcPy and I am getting errors. I have looked into the ArcSDESQLExecute sample query.

https://pro.arcgis.com/en/pro-app/latest/arcpy/classes/arcsdesqlexecute.htm

The code below is what I have written:

import arcpy

egdb = r'C:\Users\E149391\Documents\ArcGIS\Projects\ElectricMeterLatLong\esripubp.sde'
egdb_conn = arcpy.ArcSDESQLExecute(egdb)
sql = r'C:\Users\E149391\Documents\ArcGIS\Projects\ElectricMeterLatLong\CustomerDataR.sql'
egdb_return = egdb_conn.execute(sql)

CustomerDataR.sql contains:

select b.servicepointglobalid as Svc_Glbl_ID, b.facilityid, 
a.addressline1, a.addresscitystate, a.meternumber, 
a.meterpointnumber, a.premise_num, a.SERVICE_PT_NUM, d.tmsnumber 
from refgis.customerdatar a, elepub.electricmeter b, 
elepub.transformer c, elepub.transformerunit d 
where a.meterpointnumber = b.meterpointnumber and 
a.premise_num = b.premisenumber and a.service_pt_num = b.servicepointnumber and 
b.transformerglobalid = c.globalid and 
c.facilityid = d.facilityid and a.statusmeter <> 'R' and 
a.statusmeterrecord = 2 and 
a.statename = 'MO'; 

Traceback (most recent call last):
  File "<string>", line 2, in <module>
  File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\arcobjects\arcobjects.py", line
    44, in execute
    return convertArcObjectToPythonObject(self._arc_object.Execute(*gp_fixargs(args)))
AttributeError: ArcSDESQLExecute: StreamExecute ArcSDE Extended error 900 ORA-00900: invalid SQL statement

Answer 1

With the current code, this isn't a case of a SQL statement not being valid but of passing a non-SQL statement to a method expecting a SQL statement. The error message returned will be the same in both cases, but troubleshooting is different.

According to ArcSDESQLExecute - ArcGIS Pro | Documentation:

Method	Explanation
execute (sql_statement)	Sends the SQL statement to the database via an ArcSDE connection. If execute is run outside of a transaction, a commit will automatically take place once the SQL DML (INSERT, UPDATE, DELETE . . .) statement has been executed.

Looking at the current code:

import arcpy

egdb = r'C:\Users\E149391\Documents\ArcGIS\Projects\ElectricMeterLatLong\esripubp.sde'
egdb_conn = arcpy.ArcSDESQLExecute(egdb)
sql = r'C:\Users\E149391\Documents\ArcGIS\Projects\ElectricMeterLatLong\CustomerDataR.sql'
egdb_return = egdb_conn.execute(sql)

The sql variable doesn't contain a SQL statement, it contains a filesystem path to a file containing a SQL statement. To pass the SQL statement in the file to the execute command, the file has to be open and read first.

import arcpy

egdb = r'C:\Users\E149391\Documents\ArcGIS\Projects\ElectricMeterLatLong\esripubp.sde'
egdb_conn = arcpy.ArcSDESQLExecute(egdb)
sql = r'C:\Users\E149391\Documents\ArcGIS\Projects\ElectricMeterLatLong\CustomerDataR.sql'
egdb_return = egdb_conn.execute(open(sql,'r').read())

Now whether the SQL statement itself is valid, that is another question.

Answer 2

I was able to get the script to work as follows:

I was able to meet with one of my coworkers and he walked me through the steps successfully of arcpy.ArcSDESQLExecute(egdb).

import arcpy
egdb = r'C:\Users\E149391\Documents\ArcGIS\Projects\ElectricMeterLatLong\esripubp.sde' 
egdb_conn = arcpy.ArcSDESQLExecute(egdb) 
sql = ''' select b.servicepointglobalid as Svc_Glbl_ID, b.facilityid, a.addressline1, a.addresscitystate, a.meternumber, a.meterpointnumber, a.premise_num, a.SERVICE_PT_NUM, d.tmsnumber  
from refgis.customerdatar a, elepub.electricmeter b, elepub.transformer c, elepub.transformerunit d 
where a.meterpointnumber = b.meterpointnumber and a.premise_num = b.premisenumber and a.service_pt_num = b.servicepointnumber 
and b.transformerglobalid = c.globalid and c.facilityid = d.facilityid 
and a.statusmeter <> 'R' and a.statusmeterrecord = 2 and a.statename = 'MO' ''' 
egdb_return = egdb_conn.execute(sql)
for i in egdb_return:
   print('{}: {}'.format(*i))