Oracle Database Adapter Capabilities
The Oracle Database Adapter enables you to integrate the Oracle database residing behind the firewall of your
on-premises environment with Oracle Integration through use of the on-premises connectivity agent. Use the Oracle Database Adapter to poll for new and updated records for processing in Oracle Integration. For example, any new record added to the Employee table in your Oracle
database can be synchronized with Oracle HCM Cloud using Oracle Integration. In addition, use the Oracle Database 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 Database Adapter.
The Oracle Database Adapter provides the following capabilities:
-
Support for invocation of stored procedures in the Oracle database.
-
Support for non-JDBC (PL/SQL boolean, 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, andDelete.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 generating XSD from PureSQL. This feature generates an XSD from a PureSQL statement provided by dynamically querying the table.
-
Support for polling new and updated records for processing in the Oracle database. The Oracle Database Adapter supports distributed polling and multithreading. Distributed polling helps eliminate duplicate polling of the same records while multithreading provides optimum performance.
-
Support for updating or inserting multiple records in a single request.
-
Support for a logical delete polling strategy. This strategy involves updating a special field on each row once it is processed.
-
Support for performing a
SELECToperation against database tables. -
Support for database fault mapping. See Define Fault Mapping in Integrations.
-
Support for processing message payloads up to 10 MB in size. In the case of polling, you must set the Rejected Value property to REJECTED on the Polling Strategy and Options page. If the incoming message is greater than the 10 MB threshold size, that particular record is updated to REJECTED instead of READ. If the outbound operation returns a response greater than the 10 MB threshold size, the response message is ignored and a fault response is sent to the calling client.
Note:
In Java, Unicode characters are represented as 2 bytes. - Support for integrating an Oracle Database with a private endpoint. Integration is achieved with a wallet-based connection that uses the connectivity agent. See Configuring Connection Security.
The Oracle Database Adapter is one of many predefined adapters included with Oracle Integration. You can configure the Oracle Database Adapter as a connection in an integration in Oracle Integration.