Calling stored procedure (in Oracle) using ArcSDESQLExecute

StevenCase1 — Wed, 22 Jul 2020 18:58:04 GMT

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

Re: Calling stored procedure (in Oracle) using ArcSDESQLExecute

DaneHopkins — Fri, 24 Jul 2020 16:37:21 GMT

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()

topic Re: Calling stored procedure (in Oracle) using ArcSDESQLExecute in Python Questions

Calling stored procedure (in Oracle) using ArcSDESQLExecute

Re: Calling stored procedure (in Oracle) using ArcSDESQLExecute