To perform structured query language (SQL) operations in Oracle® Autonomous Data Warehouse (ADW) or relational databases as part of a chain, add a step that includes an Oracle RDBMS | ADW connection command. For bi-directional integration between Oracle relational systems and other applications, combine these commands with those for:
- Other business intelligence applications including Tableau® and Power BI®
- Performance management applications such as Anaplan®, IBM® Planning Analytics, Host Analytics®, and Oracle EPM
- Systems of record including SAP®, Workday®, and Oracle Financials Cloud
To enable these commands, an IT admin first creates an Oracle RDBMS | ADW connector.
Execute create
To execute a CREATE statement, use an Execute create command.
Properties
| Property |
Detail |
| SQL text/file |
Enter the SQL text or file to execute. |
Outputs
| Output |
Output type |
| SQL error |
JSON |
Note: SQL error returns JSON such as { "code": 102, "details": "Incorrect syntax near ']'." }.
Exit codes
| Code |
Type |
Detail |
| 0 |
Success |
Success |
| 1 |
Error |
Invalid inputs |
| 3 |
Error |
Failed to run the command |
Execute query
To execute a SELECT query and return the results as a comma-separated values (CSV) file, use an Execute query command.
Properties
| Property |
Detail |
| SQL text/file |
Enter the SQL text or file to execute. |
| Delimiter |
Select the delimiter to use in the CSV output. |
| Preview results |
To see a preview of the returned rows, check this box. |
Outputs
| Output |
Output type |
| Record count |
Integer |
| Result set (CSV) |
File |
| SQL error |
JSON |
Note: SQL error returns JSON such as { "code": 102, "details": "Incorrect syntax near ']'." }.
Exit codes
| Code |
Type |
Detail |
| 0 |
Success |
Success |
| 1 |
Error |
Invalid inputs |
| 3 |
Error |
Failed to run the command |
Execute SQL
To execute any SQL command, use an Execute SQL command. Any result set will be available as a file output.
Properties
| Property |
Detail |
| SQL text/file |
Enter the SQL text or file to execute. |
Outputs
| Output |
Output type |
| Record count |
Integer |
| Result set (CSV) |
File |
| Records updated |
Integer |
| SQL error |
JSON |
Note: SQL error returns JSON such as { "code": 102, "details": "Incorrect syntax near ']'." }.
Exit codes
| Code |
Type |
Detail |
| 0 |
Success |
Success |
| 1 |
Error |
Invalid inputs |
| 3 |
Error |
Failed to run the command |
Execute update
To execute an UPDATE statement, use an Execute update command.
Properties
| Property |
Detail |
| SQL text/file |
Enter the SQL text or file to execute. |
Outputs
| Output |
Output type |
| Records updated |
Integer |
| SQL error |
JSON |
Note: SQL error returns JSON such as { "code": 102, "details": "Incorrect syntax near ']'." }.
Exit codes
| Code |
Type |
Detail |
| 0 |
Success |
Success |
| 1 |
Error |
Invalid inputs |
| 3 |
Error |
Failed to run the command |
Get table definition
To retrieve the details of a table—including its columns, primary keys, and foreign keys—as JSON, use a Get table definition command.
Properties
| Property |
Detail |
| Table |
Enter the table to retrieve the definition of. |
Outputs
| Output |
Output type |
| Table metadata |
File |
| SQL error |
JSON |
Note: SQL error returns JSON such as { "code": 102, "details": "Incorrect syntax near ']'." }.
Exit codes
| Code |
Type |
Detail |
| 0 |
Success |
Success |
| 1 |
Error |
Invalid inputs were given |
| 3 |
Error |
Failed to run the command |
Insert records
To execute an INSERT statement, use an Insert records command. The source of the data can be either multi-lined text or a CSV file.
Properties
| Property |
Detail |
| Table |
Enter the table to insert records into. |
| CSV data source |
Enter the CSV file to insert into the table. The columns in the CSV file must match the same order specified in Columns. |
| Delimiter |
Select the delimiter used in CSV data source. |
| Is a file |
If CSV data source is a file, check this box. |
| Includes headers |
If the first row of CSV data source is the headers, check this box. |
| Columns |
Enter the list of columns that CSV data source will populate. Order is important, and should match the CSV data source. |
| Value template |
Enter the custom values declaration for the INSERT command. Indicate values with a ?. The number of ?s should match the number of fields in the target. To auto-generate, leave blank. |
Outputs
| Output |
Output type |
| Records inserted |
Integer |
| SQL error |
JSON |
Note: SQL error returns JSON such as { "code": 102, "details": "Incorrect syntax near ']'." }.
Exit codes
| Code |
Type |
Detail |
| 0 |
Success |
Success |
| 1 |
Error |
Invalid inputs |
| 3 |
Error |
Failed to run the command |
Upsert records
To execute an UPSERT statement, use an Upsert records command. If the unique keys of an existing record match those of a record in the data source, the command updates the record; otherwise, it inserts the record.
Properties
| Property |
Detail |
| Target table |
Enter the table to upsert records into. |
| Staging table (auto-generated) |
Enter the name of the table to temporarily hold records as they upsert in the target table.
Note: Do not use an existing table; the command automatically creates and then drops this table during the upsert.
|
| Unique columns |
Enter a list of the columns that contain unique values for each record. If these columns match between records in both the staging and target tables, the command updates the record; otherwise, it inserts the record. |
| CSV data source |
Enter the multi-lined text or CSV file to upsert into the target table. The columns in this data source must be in the same order as Columns. |
| Delimiter |
Select the delimiter used in CSV data source. |
| Is a file |
If CSV data source is a CSV file, check this box. |
| Includes headers |
If CSV data source starts with a header row, check this box. |
| Columns |
Enter the list of columns that CSV data source will populate, in the same order as CSV Data Source. |
| Value template |
Enter the custom values for the UPSERT statement. Indicate values with a ?. The number of ?s should match the number of fields in the target. To auto-generate, leave blank. |
Outputs
| Output |
Output type |
| Records upserted |
Integer |
| SQL error |
JSON |
Note: SQL Error returns JSON such as { "code": 102, "details": "Incorrect syntax near ']'." }.
Exit codes
| Code |
Type |
Detail |
| 0 |
Success |
Success |
| 1 |
Error |
Invalid inputs were given |
| 3 |
Error |
Failed to run the command |