Creating a SQL Task

• Console
• CLI
• API

• 1. Open the project or folder in which you want to create the task.

     For the steps to open the details page of a project or folder, see Viewing the Details of a Project or Viewing the Details of a Folder.

  2. On the project or folder details page, click Tasks.
  3. In the Tasks section, click Create task and select SQL.
  4. 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.

  5. Select the Disable simultaneous execution of the task checkbox if you want to disallow concurrent runs of this task.
  6. (Optional) For Project or folder, click Select and select a different project or folder to save the task in.
  7. To save the task for the first time, click 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.

  8. Save periodically while you work by clicking 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.

  9. 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.

     1. 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:

          1. In the Select stored procedure panel, click View all.
          2. In the Browse stored procedure panel, find the stored procedure you want and select View parameters from the Actions menu of that stored procedure.
     2. To assign parameters to the resource types:

        1. Click Assign parameter next to a resource type.

        2. 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.

           • Click Add parameter to add a parameter for the resource type.

             1. In the Add parameter panel, enter an identifier and an optional description.

             2. 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. Click 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.

             3. Click Add.

                The resource type parameter that's added is automatically selected on the Assign parameter page.

        3. Click Assign.
     3. On the Select SQL page, click Done.

        You're returned to the Create SQL task page.

  10. In the Configure parameters section, click 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:

      1. On the row of the input parameter value that you want to configure, click Configure.
      2. In the Edit parameter panel, enter a value for the input parameter and click Save.

         After configuring an input parameter, you can edit or clear the value.

      3. When you finish configuring the input parameters, click Configure.
  11. (Optional) In the Validate task section, click Validate to check the configured parameter values.

      If there are errors or warnings, click 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).
  12. When you finish configuring the task, click 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.