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