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.

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 after a SQL task is created:

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

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