Community

calling Oracle stored procedure using ArcSDESQLExecute with input and output parameters

368
1
12-17-2020 05:13 AM
VibhavathiAralaguppi
by
New Contributor
‎12-17-2020 05:13 AM

Hello,

I am trying to call Oracle stored procedure which has input and output parameters using ArcSDESQLExecute.

CALL package.procedurename(outParams, inParams)

But it is throwing an error as below -

AttributeError: ArcSDESQLExecute: StreamExecute ArcSDE Extended error 6576 ORA-06576: not a valid function or procedure name

But when we execute the Stored procedure with same parameters in Toad, there is no issue seen.

Could you please let know what could be the issue here?

Thanks,

Vibha

0 Kudos
1 Reply
BlakeTerhune
by MVP Regular Contributor
MVP Regular Contributor
‎12-18-2020 07:00 AM

None of my examples using CALL with ArcSDESQLExecute have parameters. The one place I do have parameters, I used cx_Oracle. In my case, the procedure returned a REF CURSOR so I write that data to a CSV file.

 

# Create connection
conn_str = u"{}/{}@{}".format(dbUser, dbPass, dbOracleInstance)
oracle_dbconn = cx_Oracle.connect(conn_str)

# Execute package procedure
cursor = oracle_dbconn.cursor()
ref_cursor = oracle_dbconn.cursor()
procedure = "schema.package.procedurename"
cursor.callproc(procedure, [ref_cursor, input_param])

# Process resulting ref_cursor
if not csv_fields_out:
    # Write field name header line
    csv_fields_out = [result_column[0] for result_column in ref_cursor.description]
    csv_writer.writerow(csv_fields_out)

for data_row in ref_cursor:
    # Write data lines
    csv_writer.writerow(data_row)

 

0 Kudos