1. Using the Oracle Autonomous Data Warehouse Adapter with Oracle Integration 3
  2. Understand the Oracle Autonomous Data Warehouse Adapter
  3. Oracle Autonomous Data Warehouse Adapter Capabilities

Oracle Autonomous Data Warehouse Adapter Capabilities

The Oracle Autonomous Data Warehouse Adapter enables you to integrate the Oracle Autonomous Data Warehouse database with Oracle Integration through use of direct connectivity. Use the Oracle Autonomous Data Warehouse Adapter to execute SQL queries or stored procedures in the Oracle database. For example, quotes in Oracle CPQ Cloud can be created as Orders in the on-premises Oracle database by sending SQL statements or stored procedures using the Oracle Autonomous Data Warehouse Adapter.

The Oracle Autonomous Data Warehouse Adapter provides the following benefits:

  • Support for using direct connectivity to connect to the Oracle Autonomous Data Warehouse database in place of using the on-premises connectivity agent.
  • Support for polling new and updated records for processing in the Oracle Autonomous Data Warehouse Adapter with or without use of the connectivity agent. See Perform Inbound Polling Without the Connectivity Agent.

    The Oracle Autonomous Data Warehouse Adapter supports distributed polling and multithreading. Distributed polling helps eliminate duplicate polling of the same records while multithreading provides optimum performance.

  • Support for connecting to private resources that are in your virtual cloud network (VCN) with a private endpoint. See Connect to Private Resources in Provisioning and Administering Oracle Integration 3 and Configure the Endpoint Access Type. This type of connection does not use the connectivity agent.
  • Support for integrating an Autonomous Transaction Processing database with an Oracle Cloud Infrastructure private endpoint. Integration is achieved with a wallet-based connection that uses the connectivity agent. See Configure Connection Security.
  • Support for creating integrations with Oracle Autonomous Data Warehouse.
  • Support for the bulk data import operation. This feature is supported for the following scenarios:
    • When using direct connectivity (that is, without use of the connectivity agent)
    • When using the Oracle Autonomous Data Warehouse - Shared (ADW-S) database
    • With the comma-separated value (CSV) data format only.

    A typical bulk data import use case with the Oracle Autonomous Data Warehouse Adapter consists of getting a data file from an FTP server or application (such as an HCM payroll system), placing the file in an object storage bucket, and placing the file contents into an Oracle Autonomous Data Warehouse - Shared (ADW-S) database. The file can be in a format expected by the database (for example, a CSV file) or in a format that the database is not expecting (for example, XML or JSON). In this case, Oracle Integration is used to translate the format into a CSV format that the database expects.

  • Support for invocation of stored procedures in the Oracle database.

  • Support for non-JDBC (PL/SQL record and PL/SQL table) datatypes in outbound invocations of stored procedures.

  • Support for execution of DML statements and SQL queries: Select, Insert, Update, and Delete.

    Select the Run a SQL Statement option on the Basic Info page of the Adapter Endpoint Configuration Wizard to execute simple SQL queries. For complex SQL queries, use stored procedures by selecting the Invoke a Stored Procedure option on the Basic Info page of the Adapter Endpoint Configuration Wizard. Stored procedures can reduce the complexity of a SQL query.

  • Support for updating or inserting multiple records in a single request.

  • Support for performing a SELECT operation against database tables.

  • Support for the operations on a table feature, which enables you to model SQL statements with the Adapter Endpoint Configuration Wizard. The operation on a table feature also supports multiple records in a single request.

The Oracle Autonomous Data Warehouse Adapter is one of many predefined adapters included with Oracle Integration. You can configure the Oracle Autonomous Data Warehouse Adapter as an invoke connection in an integration in Oracle Integration.

Cloud Database Connectivity Support

This table describes cloud database connectivity support for the Oracle Autonomous Data Warehouse Adapter.

Database Public Gateway Private Gateway Connectivity Agent

Oracle Autonomous Data Warehouse - public access with wallet

Supported (If access not restricted)

Supported

Supported

Oracle Autonomous Data Warehouse - private endpoint access with wallet

Not supported

Supported

Supported