PGX Data Type Compatibility and Casting

You can configure a compatibility mode for the PGX JDBC driver to determine the data type returned when calling the ResultSet#getObject() method.

The PGX JDBC driver supports the following compatibility modes:

PGX (default): By default, the PGX JDBC driver will use the PGX compatibility mode to return the PGX native types.
ORACLE_JDBC19: This mode enables the driver to return data types that are compatible with Oracle Database 19c.
ORACLE_JDBC23: This mode enables the driver to return data types that are compatible with Oracle AI Database 26ai.

The following table describes the data types returned by the driver for the different compatibility modes.

Table: Data Type Compatibility

PGX Type	PGX (Default)	ORACLE_JDBC19	ORACLE_JDBC23
`BOOLEAN`	`BOOLEAN`	`java.math.BigDecimal`	`Boolean`
`INTEGER`/`LONG`/`FLOAT`/`DOUBLE`	`INTEGER`/`LONG`/`FLOAT`/`DOUBLE`	`java.math.BigDecimal`	`java.math.BigDecimal`
`LOCAL_DATE`	`java.sql.Date`	`java.sql.Timestamp`	`java.sql.Timestamp`
`TIME`	`java.sql.Time`	Not supported	Not supported
`TIME_WITH_TIMEZONE`	`java.time.OffsetTime`	Not supported	Not supported
`TIMESTAMP`	`java.sql.Timestamp`	`oracle.sql.TIMESTAMP`	`oracle.sql.TIMESTAMP`
`TIMESTAMP_WITH_TIMEZONE`	`java.time.OffsetDateTime`	`oracle.sql.TIMESTAMPZ`	`oracle.sql.TIMESTAMPZ`

Setting the Compatibility Mode in the PGX JDBC Driver

If you wish to use a different compatibility mode (other than the default PGX mode), then this can be set as part of the properties as shown:

import static oracle.pgx.jdbc.CONNECTION_PROPERTY_COMPATIBILITY_MODE;
import static oracle.pgx.jdbc.ORACLE_JDBC19;
...
Properties properties = new Properties();
properties.put(...);
properties.put(CONNECTION_PROPERTY_COMPATIBILITY_MODE, ORACLE_JDBC19); // set compatibility mode
Connection connection = DriverManager.getConnection("<jdbc_url>", properties);

Data Type Conversions through Casting

The PGX JDBC driver allows casting of one type into another (regardless of the compatibility mode) as shown in the following table.

Table: Additional Supported Types through Casting

PGX Type	Default Mapping (PGX Compatibility)	Additional Supported Types (through Casting)
`BOOLEAN`	`BOOLEAN`	`String`, `BigDecimal`
`INTEGER`	`INTEGER`	`String`, `BigDecimal`, `Long`, `Float`, `Double`
`FLOAT`	`FLOAT`	`String`, `BigDecimal`, `Double`
`LONG`	`LONG`	`String`, `BigDecimal`, `Double`
`DOUBLE`	`DOUBLE`	`String`, `BigDecimal`
`LOCAL_DATE`	`java.sql.Date`	`String`, `java.time.LocalDate`, `java.sql.Timestamp`
`TIME`	`java.sql.Time`	`String`, `java.time.LocalTime`
`TIME_WITH_TIMEZONE`	`java.time.OffsetTime`	`String`
`TIMESTAMP`	`java.sql.Timestamp`	`String`, `java.time.LocalDateTime`, `oracle.sql.TIMESTAMP`
`TIMESTAMP_WITH_TIMEZONE`	`java.time.OffsetDateTime`	`String`, `oracle.sql.TIMESTAMPZ`

Data Type Conversions forPreparedObject#setObject(int index, Object o)

The following table describes the data type conversions when calling the PreparedObject#setObject(int index, Object o) method.

Table: Data Type Conversions for setObject Method

Given Object Type	PGX Type
`BOOLEAN`	`BOOLEAN`
`INTEGER`/`FLOAT`/`LONG`/`DOUBLE`	`INTEGER`/`FLOAT`/`LONG`/`DOUBLE`
`BIGDECIMAL`	`DOUBLE`
`java.sql.Date`, `java.time.LocalDate`	`LOCAL_DATE`
`java.sql.Time`, `java.time.LocalTime`	`TIME`
`java.time.OffsetTime`	`TIME_WITH_TIMEZONE`
`java.sql.Timestamp`, `java.time.LocalDateTime`, `oracle.sql.TIMESTAMP`	`TIMESTAMP`
`java.time.OffsetDateTime`, `oracle.sql.TIMESTAMPZ`	`TIMESTAMP_WITH_TIMEZONE`