1. Using the PostgreSQL Adapter with Oracle Integration 3
  2. Understand the PostgreSQL Adapter
  3. PostgreSQL Adapter Capabilities

PostgreSQL Adapter Capabilities

The PostgreSQL Adapter enables you to integrate a PostgreSQL cloud database with Oracle Integration. In addition, the PostgreSQL Adapter enables you to integrate a PostgreSQL database residing behind the firewall of your on-premises environment with Oracle Integration through use of the on-premises connectivity agent. You can configure the PostgreSQL Adapter as a trigger or an invoke connection in an integration in Oracle Integration.

The PostgreSQL Adapter provides the following benefits:

For trigger endpoints:
  • Support for polling new and updated records for processing in the PostgreSQL Adapter without use of the connectivity agent. See Perform Inbound Polling Without the Connectivity Agent.
  • Supports polling new and updated records for processing in the PostgreSQL on-premises database with the connectivity agent. The PostgreSQL Adapter supports distributed polling. Distributed polling provides high availability and improves performance.
  • Supports a logical delete polling strategy. This strategy involves updating a special field on each row once it is processed.
  • Supports 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 10 MB, that particular record is updated to REJECTED instead of READ. If the message payload is greater than 10 MB, a fault response is sent to the calling clients.
  • Supports connectivity to a PostgreSQL cloud/on-premise database over SSL through the connectivity agent.
For invoke endpoints:
  • Supports bulk data import by selecting the Perform Bulk Data Import Operation on the Basic Info page of the Adapter Endpoint Configuration Wizard. See Import Data Files Using the Bulk Data Import Operation and Import Transactional Data Using the Bulk Data Import Operation.

  • Supports invocation of stored procedures in the PostgreSQL database.

  • Supports execution of DML statements such as Select, Insert, Update, and Delete using the Run a SQL Statement option.

    Select Run a SQL Statement on the Basic Info page of the Adapter Endpoint Configuration Wizard to execute simple SQL queries.

  • Supports performing the Select, Insert, Update, and Merge operations against database tables.

    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.

  • Supports pagination. You can implement pagination when fetching a large number of records for a Select query and receive sorted data in chunks. See Use Pagination in an Integration.
  • Supports connectivity to a PostgreSQL cloud/on-premises database over SSL through the connectivity agent.

The PostgreSQL Adapter is one of many predefined adapters included with Oracle Integration.

Supported Data Types for the Stored Procedure and Operation On Table Operations

The PostgreSQL Adapter supports the following data types for stored procedure and Operation On Table operations.

  • int
  • int2
  • int4
  • int8
  • integer
  • bigInt
  • smallint
  • numeric
  • double precision
  • real
  • float
  • float4
  • float8
  • money
  • decimal
  • oid
  • varchar
  • char
  • text
  • bpchar
  • name
  • date
  • timestamp
  • timestamptz
  • time
  • timetz
  • boolean