This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.)
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.
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.
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.
Alternately, you can call the stored procedure from the SQL Editor tab, using this format: