AnsweredAssumed Answered

Calling stored procedure (in Oracle) using ArcSDESQLExecute

Question asked by smcase2 on Jul 22, 2020
Latest reply on Jul 24, 2020 by mdhopkin1

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

Outcomes