Creating a SQL Task

A SQL task in Data Integration is associated with a stored procedure in a database.

The database must be associated with a data asset that's already created in the workspace. To create a data asset, see Creating a Data Asset.

Create a SQL task in a project or folder. Data Integration includes one default project to get you started. To create another project or folder, see Projects and Folders.

In Data Integration, by default you can have simultaneous or parallel task runs of a task at a given time. To disallow concurrent task runs that are initiated manually, select the Disable simultaneous execution of the task checkbox when you create the task. When simultaneous task runs are disallowed, a run request for the task fails if there's already a task run in progress that's in a non-terminal state.

1. On the Tasks page of the project or folder in which you want to create a task, select Create task and then select SQL. If you need help finding the Tasks page in a project or folder, see Listing Tasks.
2. On the Create SQL task page, enter a name and an optional description.
  
  The identifier is a system-generated value based on the name. You can change the value, but after you create and save the task, you can't update the identifier.
3. Select the Disable simultaneous execution of the task checkbox if you want to disallow concurrent runs of this task.
4. (Optional) For Project or folder, click Select and select a different project or folder to save the task in.
5. To save the task for the first time, select one of the following buttons:
  
  Create: Creates and saves the task. You can continue to create and edit the task.
  
  Create and close: Creates and saves the task, closes the page, and returns you to the tasks list on the project or folder details page.
6. Save periodically while you work by selecting one of the following buttons:
  
  Save: Commits changes since the last save. You can continue editing after saving.
  
  Save and close: Commits changes, closes the page, and returns you to the tasks list on the project or folder details page.
  
  Save as: Commits changes (since the last save) and saves to a copy instead of overwriting the current task. You can provide a name for the copy and select a different project or folder for the copy, or save the copy in the same project or folder as the existing task.
7. In the SQL section, click Select and select the data asset that's associated with the database in which the stored procedure that you want to use is saved.
  
  On the Select SQL page that appears, select the types directly or parameterize the resource types only in the order as the types are shown.
  
  To find and select resource types directly, click Select next to a type when it's available for selection.
  
  Data asset: The name of the data asset.
  
  Connection: The connection to use to access the database that's associated with the data asset.
  
  Schema: The schema that owns the stored procedure.
  
  Stored procedure: The name of the stored procedure to use for this SQL task.
  
  To view the parameters in a stored procedure before you select the procedure:
  
  In the Select stored procedure panel, select View all.
  
  In the Browse stored procedure panel, find the stored procedure you want and select View parameters from the Actions menu () of that stored procedure.
  
  To assign parameters to the resource types:
  
  Select Assign parameter next to a resource type.
  
  On the Assign parameter page, perform one of the following actions:
  
  Select a parameter from the list. Only parameters of the same resource type appear in the list for selection.
  
  Select Add parameter to add a parameter for the resource type.
  
  In the Add parameter panel, enter an identifier and an optional description.
  
  Set the default value of the parameter for the resource type.
  
  For the stored procedure type, you can view the parameters in a stored procedure before you select the procedure. Select View all that's next to the Stored procedure menu. In the Browse stored procedure panel that appears, find the stored procedure you want and select View parameters from the Actions menu () of that stored procedure.
  
  Select Add.
  
  The resource type parameter that's added is automatically selected on the Assign parameter page.
  
  Select Assign.
  
  On the Select SQL page, select Done.
  
  You're returned to the Create SQL task page.
8. In the Configure parameters section, select Configure to review the list of parameters and configure their values.
  
  The number of parameters in the stored procedure is shown next to Parameters configured. Only input parameter values in the stored procedure can be configured.
  
  The configured value of an input parameter must match the defined data type of that parameter. For example, you can't provide a STRING value for an input parameter whose data type is NUMERIC.
  
  On the Configure stored procedure parameters page, configure a parameter value by following these steps:
  
  On the row of the input parameter value that you want to configure, select Configure.
  
  In the Edit parameter panel, enter a value for the input parameter and select Save.
  
  After configuring an input parameter, you can edit or clear the value.
  
  When you finish configuring the input parameters, select Configure.
9. (Optional) In the Validate task section, select Validate to check the configured parameter values.
  
  If there are errors or warnings, select View messages. Resolve any errors before you publish the task.
  
  Note
  
  A SQL task fails to run if NUMERIC data type input parameters have NULL as the configured default values. To avoid task run failures, change a NULL value to 0 (zero).
10. When you finish configuring the task, select Create and Close or Save and close.
Publish the SQL task to an application in Data Integration before you run the task or schedule the task for running. For publishing information, see Publishing to a Data Integration Application.
Use the oci data-integration task create-task-from-sql-task command and required parameters to create a SQL task:
oci data-integration task create-task-from-sql-task [OPTIONS]
For a complete list of flags and variable options for CLI commands, see the Command Line Reference.
Run the CreateTask operation with the appropriate resource subtype to create a SQL task.

Oracle Cloud Infrastructure Documentation

Creating a SQL Task