calling Oracle stored procedure using ArcSDESQLExecute with input and output parameters

VibhavathiAralaguppi — Thu, 17 Dec 2020 13:13:56 GMT

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

Re: calling Oracle stored procedure using ArcSDESQLExecute with input and output parameters

BlakeTerhune — Fri, 18 Dec 2020 15:01:57 GMT

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)

topic calling Oracle stored procedure using ArcSDESQLExecute with input and output parameters in Python Questions

calling Oracle stored procedure using ArcSDESQLExecute with input and output parameters

Re: calling Oracle stored procedure using ArcSDESQLExecute with input and output parameters