Environment Details
When running a workflow with an Input or Output Data tool and trying to execute a procedure via the Pre or Post SQL Statement section, the following error occurs:
ORA-00900: invalid SQL Statement
- Alteryx Designer
- Oracle
- PL/SQL
- Anonymous Block
- Execute
- Call
Cause
Using an
anonymous block or
execute command in the Pre or Post SQL Statement window of either of the Input Data or Output Data tools within the workflow.
OR
EXEC test_sp_1;The Pre and Post SQL Statement windows do not support anonymous blocks nor does it support the SQL Plus command
EXEC, which is a shortcut for an anonymous block.
Resolution
Use the
CALL command and always include the open and closing parenthesis, even if there are no parameters to pass in the Procedure. See the following examples:
Standard syntax:
CALL SCHEMA.MYPROC()
Syntax when using parameters:
CALL SCHEMA.MYPROC(10, 20, my_var)
Note
- Ensure there's a space between each parameter values. Placing a semi-colon at the end is optional.
- Ensure that the proper permissions are granted to the user for the stored procedure.
- This is an example of a successful status in the Workflow Results window: