Calling stored procedure (in Oracle) using ArcSDESQLExecute

[StevenCase1]

Hi all, I'm trying to use a python script to make a call to an oracle database via an sde connection file to execute a stored procedure within the database. According to this thread I found it should be possible according to user "cbutler_1", however regardless of my attempts to correctly format the sql statement this is the error I continue to get:

AttributeError: ArcSDESQLExecute: StreamExecute ArcSDE Extended error 911 ORA-00911: invalid character

Here is how I'm using the ArcSDESQLExecute method to make the call via the sde connection file:

sql_conn = arcpy.ArcSDESQLExecute(<sde connectionfile path>)

   sql = 'CALL <packagename>.<procedure name>(<parameters>)'

   success = sql_conn.execute(sql)

Here is the the argument that is being passed to the ArcSDESQLExecute method (found via debugging):

• CALL WORKORDERPACKAGE.CreateWorkOrder('20200722','Leak Survey 3 Year','{"Area": "Map 49", "ScheduledStartDate": "12/31/2019", "ScheduledFinishDate": "7/31/2020", "Assignments": ["AllWorkers"], "FieldValues": {"PROJECT_NAME": "0318_TestWO_009", "WorkOrderComment": "", "WorkOrderEquipment": [""]}}',1);

• It's worth noting that I can paste the above statement into SQL developer and run it without issue, so I'm wondering if perhaps the error arises in how the statement is being handed off from the ArcSDESQLExecute method through the sde connection

[DaneHopkins · Accepted solution]

To resolve this remove the semi-colon in your argument.

CALL WORKORDERPACKAGE.CreateWorkOrder('20200722','Leak Survey 3 Year','{"Area": "Map 49", "ScheduledStartDate": "12/31/2019", "ScheduledFinishDate": "7/31/2020", "Assignments": ["AllWorkers"], "FieldValues": {"PROJECT_NAME": "0318_TestWO_009", "WorkOrderComment": "", "WorkOrderEquipment": [""]}}',1)

Also add a commitTransaction()

sql_conn = arcpy.ArcSDESQLExecute(<sde connectionfile path>)

   sql = 'CALL <packagename>.<procedure name>(<parameters>)'

   success = sql_conn.execute(sql)

   if success:

        sql_conn.commitTransaction()