Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How To: Add Exception Handling to Oracle Pre or Post SQL

Alteryx
Alteryx
Created

How To: Add Exception Handling to Oracle Pre or Post SQL

 

Exception handling may be needed so that a scheduled workflow continues even if a pre or post Oracle SQL statement has an error.

Since PL/SQL currently is not supported in the SQL Editor, you will need to create a stored procedure for exception handling and call the stored procedure in the pre or post SQL section.

 

This example shows how to create a stored procedure that handles exceptions for a delete table statement when the table was previously removed and does not exist. (Oracle currently does not have an IF EXISTS option.)

 

Procedure

 

Adding a parameter for the table name is recommended so that one stored procedure can be reused for any table.

 

Note: You will need to create procedure permission on the Oracle database, contact a DBA if you are lacking this permission. 

 

Use this format in your SQL editor for the stored procedure. Notice a space is added at the end of the SQL command ‘DROP TABLE ’ followed by || for concatenation and the parameter name used as a place holder for the table name. 

 

EricWe_0-1578416450729.png

 

Once the stored procedure exists on the database, you can reference it in the Pre SQL or Post SQL sections of the Input Data or Output Data Tool. Click on the Ellipsis button to open the section. 

 

EricWe_3-1578417242409.png

 

On the Stored Procedures tab, just highlight the stored procedure name on the left, and then in the Value text box, type in the table name surrounded by single quotes.

 

EricWe_1-1578416450737.png

 

Alternately, you can call the stored procedure from the SQL Editor tab, using this format:

 

EricWe_2-1578416450742.png

 

Additional Resources

 

Input Data Tool 

Output Data Tool