topic Re: SQL Custom Step - Stored Procedure in ArcGIS Workflow Manager Questions

SQL Custom Step - Stored Procedure

Jean-GabrielHasbani — Wed, 29 Feb 2012 18:35:47 GMT

Hello,

I am trying to run a stored procedure, in Oracle, using the SQL custom step. I have WMX 10, Desktop. WMX is installed on the same DB as the stored procedure, I and WMX have execute rights on the procedure.

When running the step, I get the error "An error occurred while trying to execute this step. Underlying DBMS error [Ora-00900: Invalid SQL statement]"

It is not a coding error as I can successfully run the procedure from Oracle, as the WMX user. It is not a permission issue as, for testing, I gave public execution rights on the procedure.

The step is declared as:
/database:jtxsystem /sql:"execute Shema.Package.ProcedureName(param1,param2)"

I have tried, with no luck,the Data DB (same as the jtxsystem DB anyways), and with/without an ending ;

Has anyone succeeded in running a stored procedure from a custom SQl step?

Thanks

Re: SQL Custom Step - Stored Procedure

TopeBello — Tue, 06 Mar 2012 15:08:52 GMT

Hi Jean,

Yes, and this is the argument that you can pass into the custom step

/database:jtxsystem /sql:"BEGIN Shema.Package.ProcedureName(param1,param2); END;"

You can also take a look at this forum post here that is related to what you are doing.
I hope this helps!

Thanks,
Tope

Re: SQL Custom Step - Stored Procedure

Jean-GabrielHasbani — Thu, 08 Mar 2012 18:24:29 GMT

Thank you very much! It works perfectly with this syntax!

Jean