Custom SQL Task for Executing Jobs

Earlier we saw how the Events Service can be used to send notifications when tasks complete or on error, some of those examples were using OCI Functions to provide the custom logic. In this example we will see how SQL Tasks can be created to execute jobs in OCI and invoke arbitrary OCI functionality — this example pushes a notification to OCI Notifications service. We can do all kinds of interesting things in OCI easily using SQL procedures and lots and lots of people know PLSQL. Every ADB is automatically updated with the latest revision of the OCI SDKs so you have the OCI world at your finger tips.

Below you can see a procedure that creates a procedure to execute a data integration task and then wait on it to complete then post a notification on error. The notification has a useful title “Error executing task <taskname>” and the body of the email includes;

• the task run key
• the error message itself
• the URL to the task run in the OCI Console

The procedure has a few parameters which include the workspace OCID, the application and task name, the ADB credential name for accessing the OCI resources and the region to use. The procedure also takes an option topic ocid, you can optionally have the message posted along with a max_wait_time.

You can then create SQL Tasks in Data Integration that invoke this procedure;

Configuring the procedure with the values needed;

There are a lot of different examples that can be done with this now, you could call this procedure from other procedures and easily do other activities (chain 2 together for example, or rename Object Storage folders before/after the execution).