18.6.2 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 Database 23ai.

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

Table 18-4 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 18-5 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 for PreparedObject#setObject(int index, Object o)

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

Table 18-6 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