Creating SQL Tasks

A SQL task lets you run a SQL object in pipeline.

Currently, the only SQL objects you can run are stored procedures. For a list of supported databases, see Supported Data Sources for SQL Tasks.

You create a SQL task by selecting a stored procedure. The stored procedure must exist in the data source that's associated with a data asset already created in your workspace. The variables defined in a stored procedure are exposed as input, output, and in-out parameters in a SQL task. You can view the parameters of a stored procedure before you select the procedure. For a list of supported SQL data types that can be used as SQL task parameters, see Supported SQL Data Types.

When you create a SQL task, you can configure values for input parameters only. If input parameters are configured in a SQL task, you can override the default values when you configure the SQL task in a pipeline, and when you run a pipeline that includes the SQL task. In a pipeline, downstream tasks and operations can use the outputs from an upstream SQL task.

Supported Data Sources for SQL Tasks

You can execute SQL stored procedures that are saved in certain databases.

Currently, stored procedures in the following databases are supported:

  • Oracle Database (except 11g)
  • Oracle Autonomous Data Warehouse
  • Oracle Autonomous Transaction Processing

For supported versions, see Supported data sources and their versions.

Supported SQL Data Types

Variables defined in SQL stored procedures are exposed as parameters in the SQL tasks you create or run.

The following SQL data types are supported for input and output parameters when you use stored procedures in SQL tasks:

SQLJDBC/Java
VARCHARjava.lang.String
CHARjava.lang.String
LONGVARCHARjava.lang.String
BITboolean
NUMERICjava.math.BigDecimal
TINYINTbyte
SMALLINTshort
INTEGERint
BIGINTlong
REALfloat
FLOATfloat
DOUBLEdouble
VARBINARYbyte[ ]
BINARYbyte[ ]
DATEjava.sql.Date
TIMEjava.sql.Time
TIMESTAMPjava.sql.Timestamp

The following SQL data types are not yet supported:

SQLJDBC/Java
ARRAYjava.sql.Array
REFjava.sql.Ref
STRUCTjava.sql.Struct
CLOBjava.sql.Clob
BLOBjava.sql.Blob

Creating a SQL Task

A SQL task lets you run or reuse custom SQL in a pipeline.

You select a SQL stored procedure when you create a SQL task. The stored procedure must exist in a data asset you have created in your workspace. You provide information about the data asset and stored procedure in the following order:

  • The name of the data asset that has the stored procedure you want to run
  • The connection that can be used to access the data source associated with the data asset
  • The schema that owns the stored procedure
  • The name of the stored procedure to run

For supported data sources, see Supported data sources for SQL tasks.

You create a SQL task in a project or folder. Data Integration includes one default project to get you started, but if you want to create your own, see Using Projects and Folders.

To create a SQL task:

  1. On the workspace home page, in the Quick actions tile, select Create SQL task.

    Alternatively, you can navigate to a project or folder, and click Tasks on the project or folder details page. Then select SQL from the Create task menu.

  2. On the Create SQL task page, enter a name and description (optional).

    The Identifier field is a system-generated value based on the value you enter for Name. You can change the generated value, but after you save the task, you cannot change the value again.

  3. For Project or folder, click Select, and select the project or folder to save your SQL task to.

    If you're creating this task from a project or folder details page, this field is auto-populated for you.

  4. In the SQL section, click Select.
  5. In the Select SQL page, select the data asset, connection, schema, and then the stored procedure only in the order as displayed. Click Select next to a resource type to use a panel and a resource menu to select the resource. You can also:
    • Assign a parameter to a resource. See Assigning a Parameter and Adding a Parameter.
    • View the parameters in a stored procedure before you select the procedure:
      1. In the Select stored procedure panel, select View all from the Stored Procedure menu.
      2. In the Browse stored procedure panel, locate the stored procedure you want and select View parameters from the Actions menu for that stored procedure.
      3. Review the list of parameters in the stored procedure, then click Close.
  6. Click Done when you have finished selecting a data asset, connection, schema, and stored procedure.
  7. In the Configure parameters section, click Configure to view or configure values for the stored procedure parameters.
    Note that the number of configured parameters is shown next to Parameters Configured.
  8. (Optional) In the Configure stored procedure parameters page, review the list of parameters in the stored procedure. Only input parameters can be configured.
    Note

    The configured value of an input parameter must match the defined data type of that parameter. For example, you cannot provide a String value for an input parameter whose data type is NUMERIC. Also, the configured value of a NUMERIC data type input parameter cannot be NULL at runtime.
    1. In the row of the input parameter value you want to configure, click Configure.
    2. In the Edit parameter panel, enter a value for that input parameter and click Save.
    3. After configuring an input parameter, you can Edit or Clear the configured value.
    4. When you have completed configuring the input parameters, click Configure.
  9. (Optional) In the Validate task section, click Validate to check for errors and warnings in the configured parameter values.
    Note

    A SQL task fails to run if there are configured NUMERIC data type input parameters with NULL as the default value. To avoid task run failures, change a NULL value to 0 (zero).
  10. To save the task for the first time, you can click:
    • 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 project or folder details tasks page or workspace home page.
  11. Save periodically while you work. You can click:
    • Save: Commits changes since your last save. You can continue editing after saving.
    • Save and close: Commits changes, closes the page, and returns you to the project or folder details tasks page or workspace home page.
    • Save as: Commits changes (since your last save) and saves to a copy instead of overwriting the current task. You can provide a name for the copy, then select a different project or folder for the copy, or save the copy in the same project or folder as the existing task.
  12. When you have completed working on your task, click Create and Close or Save and close.
Publish the SQL task to an application in Data Integration before you can run the task, schedule the task for running, or use the task in a pipeline.

Editing a SQL Task

You can edit a SQL task and modify the configured parameter values in the associated stored procedure.

You edit a SQL task from the project or folder details page where you saved the task to.

  1. Click Open tab (plus icon) in the tab bar, and select Projects.
  2. On the Projects page, select the project containing the SQL task you want to edit.
    If the task is saved to a folder in this project, click Folders and select the folder that contains the SQL task you want to edit.
  3. Click Tasks on the project or folder details page.
  4. From the tasks list, select View details from the Actions menu of the task that you want to edit.
  5. Edit the fields or sections you want to change.
  6. Save periodically while you work. You can click:
    • Save: Commits changes since your last save. You can continue editing after saving.
    • Save and close: Commits changes, closes the page, and returns you to the project or folder details tasks page.
    • Save as: Commits changes (since your last save) and saves to a copy instead of overwriting the current task. You can provide a name for the copy, then select a different project or folder for the copy, or save the copy in the same project or folder as the existing task.

Deleting a SQL Task

If you want to delete a SQL task, you can do so from the tasks list on a project or folder details page. After a task is deleted, it cannot be restored.

To delete a SQL task:

  1. Click Open tab (plus icon) in the tab bar, and select Projects.
  2. On the Projects page, select the project containing the SQL task you want to delete.
    If the task is saved to a folder in this project, click Folders and select the folder that contains the SQL task you want to delete.
  3. Click Tasks on the project or folder details page.
  4. From the tasks list, select Delete from the Actions menu of the task that you want to delete.
  5. In the Delete task dialog, confirm that you want to delete the task, and then click Delete.