Oracle Cloud Infrastructure Documentation

Working with SQL Tasks

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

Currently, the only SQL objects that you can run are stored procedures. The stored procedure must exist in a data source that's associated with a database data asset in the workspace. For a list of supported databases, see Supported Data Sources for SQL Tasks.

The variables defined in the stored procedure are exposed as IN (input), OUT (output), and IN-OUT (input-output) parameters in the SQL task. For a list of the supported SQL data types that can be used as SQL task parameters, see Supported SQL Data Types.

Note

If you're using the fields query parameter in a stored procedure, see Improving Performance.

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

The following pages describe how you can create, edit, and delete SQL tasks in Data Integration:

The following pages describe other management tasks that can be performed:

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 AI Lakehouse
  • Oracle Autonomous AI 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 that you create or run.

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

SQL JDBC/Java
VARCHAR java.lang.String
CHAR java.lang.String
LONGVARCHAR java.lang.String
BIT boolean
NUMERIC java.math.BigDecimal
TINYINT byte
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOAT float
DOUBLE double
VARBINARY byte[ ]
BINARY byte[ ]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp

The following SQL data types are not yet supported:

SQL JDBC/Java
ARRAY java.sql.Array
REF java.sql.Ref
STRUCT java.sql.Struct
CLOB java.sql.Clob
BLOB java.sql.Blob

Improving Performance

Specify the value metadata in the fields query parameter to retrieve minimal information. This uses fewer system resources, leading to faster retrieval times.

For example, when using the LIST_PUBLISHED_OBJECTS function, specify the value metadata in the fields query parameter to retrieve a list of all the published objects for a specified application.