Skip to main content

Oracle type support

Foundations JDBC supports Oracle data types, including OBJECT types, nested tables, intervals, and LOB types.

Numeric types​

Universal NUMBER type​

Oracle TypeJava TypeNotes
NUMBERBigDecimalArbitrary precision
NUMBER(p,0) where p <= 9Integer32-bit integer
NUMBER(p,0) where 9 < p <= 18Long64-bit integer
NUMBER(p,s)BigDecimalFixed precision/scale
val numberType: OracleType<BigDecimal> = OracleTypes.number
val decimal: OracleType<BigDecimal> = OracleTypes.numberOf(10, 2)  // NUMBER(10,2)
val intType: OracleType<Int> = OracleTypes.numberAsInt(9)        // NUMBER(9)
val longType: OracleType<Long> = OracleTypes.numberAsLong(18)    // NUMBER(18)

IEEE 754 floating point​

Oracle TypeJava TypeNotes
BINARY_FLOATFloat32-bit IEEE 754
BINARY_DOUBLEDouble64-bit IEEE 754
FLOAT(p)DoubleMaps to NUMBER internally
val binaryFloat: OracleType<Float> = OracleTypes.binaryFloat
val binaryDouble: OracleType<Double> = OracleTypes.binaryDouble
val floatType: OracleType<Double> = OracleTypes.float_Of(126)  // FLOAT(126)

Boolean type​

Oracle TypeJava TypeNotes
BOOLEANBooleanOracle 23c+ native
NUMBER(1)BooleanTraditional 0/1 convention
val boolNative: OracleType<Boolean> = OracleTypes.boolean_        // Oracle 23c+
val boolNumber: OracleType<Boolean> = OracleTypes.numberAsBoolean // NUMBER(1)

Character types​

Oracle TypeJava TypeMax LengthNotes
VARCHAR2(n)String4000 bytesVariable-length
CHAR(n)String2000 bytesFixed-length, blank-padded
NVARCHAR2(n)String4000 bytesNational character set
NCHAR(n)String2000 bytesNational fixed-length
LONGString2 GBDeprecated, use CLOB
val varcharType: OracleType<String> = OracleTypes.varchar2
val varchar100: OracleType<String> = OracleTypes.varchar2Of(100)
val charType: OracleType<String> = OracleTypes.char_Of(10)
val nvarcharType: OracleType<String> = OracleTypes.nvarchar2Of(100)

Non-empty string variants​

For NOT NULL columns, use NonEmptyString to guarantee non-empty values:

val nonEmpty: OracleType<NonEmptyString> = OracleTypes.varchar2NonEmpty(100)
val nvarNonEmpty: OracleType<NonEmptyString> = OracleTypes.nvarchar2NonEmpty(100)

Padded string for CHAR​

For CHAR columns preserving padding:

val padded: OracleType<PaddedString> = OracleTypes.charPadded(10)  // CHAR(10)
val npadded: OracleType<PaddedString> = OracleTypes.ncharPadded(10) // NCHAR(10)

Large object (LOB) types​

Oracle TypeJava TypeMax SizeNotes
CLOBString4 GBCharacter LOB
NCLOBString4 GBNational character LOB
BLOBbyte[]4 GBBinary LOB
val clobType: OracleType<String> = OracleTypes.clob
val nclobType: OracleType<String> = OracleTypes.nclob
val blobType: OracleType<ByteArray> = OracleTypes.blob

// Non-empty variants
val clobNonEmpty: OracleType<NonEmptyString> = OracleTypes.clobNonEmpty
val blobNonEmpty: OracleType<NonEmptyBlob> = OracleTypes.blobNonEmpty

Binary types​

Oracle TypeJava TypeMax LengthNotes
RAW(n)byte[]2000 bytesVariable-length binary
LONG RAWbyte[]2 GBDeprecated, use BLOB
val rawType: OracleType<ByteArray> = OracleTypes.raw
val raw100: OracleType<ByteArray> = OracleTypes.rawOf(100)  // RAW(100)

// Non-empty variant
val rawNonEmpty: OracleType<NonEmptyBlob> = OracleTypes.rawNonEmpty(100)

Date/time types​

Oracle TypeJava TypeNotes
DATELocalDateTimeDate + time, second precision — Oracle's DATE always has a time component
TIMESTAMPLocalDateTimeNaive timestamp, no zone (default precision 6)
TIMESTAMP WITH TIME ZONEZonedDateTimePreserves offset or zone region — see note below
TIMESTAMP WITH LOCAL TIME ZONEInstantUTC instant, presented in session zone — see note below
val dateType: OracleType<LocalDateTime> = OracleTypes.date
val tsType: OracleType<LocalDateTime> = OracleTypes.timestamp
val ts3: OracleType<LocalDateTime> = OracleTypes.timestampOf(3)  // TIMESTAMP(3)
val tstz: OracleType<ZonedDateTime> = OracleTypes.timestampWithTimeZone // preserves zone regions
val tsltz: OracleType<Instant> = OracleTypes.timestampWithLocalTimeZone

Note: Oracle DATE includes time (unlike SQL standard), so it maps to LocalDateTime, not LocalDate.

TIMESTAMP WITH TIME ZONE → ZonedDateTime (not OffsetDateTime)

Oracle's TSTZ column uses a 13-byte on-disk format that can hold either a fixed offset (-08:00) or a named zone region (America/Los_Angeles). Region names are DST-aware — the same America/Los_Angeles column value renders as -08:00 in January and -07:00 in July.

OffsetDateTime cannot represent named zones — it holds only a numeric offset. Mapping Oracle TSTZ to OffsetDateTime would silently collapse every region to its current offset, so a round-trip of 2024-01-15T10:00 America/Los_Angeles would come back as 2024-01-15T10:00-08:00 with the region erased — the DST rule is lost for any future reads.

ZonedDateTime covers both cases without information loss:

  • Fixed-offset input (ZonedDateTime.of(..., ZoneOffset.UTC)) round-trips as a ZonedDateTime whose zone is a ZoneOffset.
  • Named-region input (ZonedDateTime.of(..., ZoneId.of("America/Los_Angeles"))) round-trips as a ZonedDateTime whose zone is a ZoneRegion.

If you don't need the region — if you're just modelling "a moment in time with whatever zone the user was in" — prefer TIMESTAMP WITH LOCAL TIME ZONE (see below) and get an Instant, which is simpler.

TIMESTAMP WITH LOCAL TIME ZONE → Instant (not LocalDateTime)

Despite the "LOCAL TIME ZONE" name, this column stores a universal instant, not a naive wall-clock. Oracle's documentation: "data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When users retrieve the data, Oracle Database returns it in the users' local session time zone."

In other words: the column stores an instant, and Oracle applies session-TZ rendering at read-time as a display convenience. A value inserted from an Asia/Tokyo session reads back as the same instant from an America/Los_Angeles session, in LA's local time — instant identity is preserved.

Instant is the Java type with this exact semantic: a point in time, zone-free. LocalDateTime would be wrong — it claims "naive wall-clock with no zone", but the data genuinely is a universal moment. Using LocalDateTime would mean two clients with different session-TZ settings would mint different LocalDateTime values for the same row, destroying round-trip stability.

Same mapping as PostgreSQL's timestamptz and DuckDB's TIMESTAMPTZ — all three store a universal instant and use Instant in Java.

Interval types​

Oracle TypeJava TypeNotes
INTERVAL YEAR TO MONTHOracleIntervalYMYears and months
INTERVAL DAY TO SECONDOracleIntervalDSDays, hours, minutes, seconds
val ymType: OracleType<OracleIntervalYM> = OracleTypes.intervalYearToMonth
val ym4: OracleType<OracleIntervalYM> = OracleTypes.intervalYearToMonth(4)

val dsType: OracleType<OracleIntervalDS> = OracleTypes.intervalDayToSecond
val ds96: OracleType<OracleIntervalDS> = OracleTypes.intervalDayToSecond(9, 6)

// Create and use intervals
val interval: OracleIntervalYM = OracleIntervalYM.parse("+02-05")  // 2 years, 5 months
val oracle: String = interval.toOracleFormat()  // "+02-05"
val iso: String = interval.toIso8601()          // "P2Y5M"

ROWID types​

Oracle TypeJava TypeNotes
ROWIDStringPhysical row address (18 chars)
UROWIDStringUniversal ROWID (max 4000 bytes)
val rowidType: OracleType<String> = OracleTypes.rowId
val urowidType: OracleType<String> = OracleTypes.uRowId
val urowid1000: OracleType<String> = OracleTypes.uRowId(1000)

XML and JSON types​

Oracle TypeJava TypeNotes
XMLTYPEStringXML document storage
JSONJsonNative JSON (Oracle 21c+)
val xmlType: OracleType<String> = OracleTypes.xmlType
val jsonType: OracleType<Json> = OracleTypes.json

val data: Json = Json("{\"name\": \"Oracle\"}")

Object types​

Oracle OBJECT types are built from a RowCodecNamed via compositeOf:

OracleType<Address> addressType = OracleTypes.compositeOf("ADDRESS_T",
    RowCodec.<Address>namedBuilder()
        .field("STREET", OracleTypes.varchar2Of(200), Address::street)
        .field("CITY", OracleTypes.varchar2Of(100), Address::city)
        .field("ZIP", OracleTypes.varchar2Of(10), Address::zip)
        .build(Address::new));

The returned OracleType can be used with OracleVArray and OracleNestedTable for collection columns.

VARRAYs​

VARRAYs are fixed-maximum-size ordered collections (CREATE TYPE ... AS VARRAY(n) OF ...). Mapped to List<T> in Java. The max size is enforced on write.

// CREATE TYPE phone_list AS VARRAY(5) OF VARCHAR2(25);
val phoneList: OracleType<List<String>> =
    OracleVArray.of("PHONE_LIST", 5, OracleTypes.varchar2Of(25))

// CREATE TYPE score_array AS VARRAY(100) OF NUMBER;
val scores: OracleType<List<BigDecimal>> =
    OracleVArray.of("SCORE_ARRAY", 100, OracleTypes.number)

Nested tables​

Nested tables are unbounded collections (CREATE TYPE ... AS TABLE OF ...). Like VARRAYs, they map to List<T> but have no size limit. Nested tables can hold OBJECT types for complex hierarchical data.

// CREATE TYPE order_item_t AS OBJECT (
//     product_name VARCHAR2(100),
//     quantity     NUMBER(10),
//     unit_price   NUMBER(12,2)
// );
data class OrderItem(val productName: String, val quantity: Int, val unitPrice: BigDecimal)

val orderItemType: OracleType<OrderItem> =
    OracleTypes.compositeOf(
        "ORDER_ITEM_T",
        RowCodec.namedBuilder<OrderItem>()
            .field("PRODUCT_NAME", OracleTypes.varchar2Of(100), OrderItem::productName)
            .field("QUANTITY", OracleTypes.numberAsInt(10), OrderItem::quantity)
            .field("UNIT_PRICE", OracleTypes.numberOf(12, 2), OrderItem::unitPrice)
            .build(::OrderItem))

// CREATE TYPE order_items_t AS TABLE OF order_item_t;
val orderItems: OracleType<List<OrderItem>> =
    OracleNestedTable.of("ORDER_ITEMS_T", orderItemType)

Nullable types​

Any type can be made nullable using .opt():

val notNull: OracleType<Int> = OracleTypes.numberInt
val nullable: OracleType<Int?> = OracleTypes.numberInt.opt()

Oracle nullability behavior​

Oracle treats empty strings as NULL — INSERT INTO t (col) VALUES ('') stores NULL. This means VARCHAR2 columns are effectively always nullable from Oracle's perspective, even if the column has a NOT NULL constraint (an empty string insert will fail with a constraint violation, not store an empty string).

When using query analysis, Oracle may report all VARCHAR2/CHAR columns as nullable. Use .nullableOk() on the type if you want to suppress nullability warnings for columns you know are NOT NULL in the schema:

OracleType<String> name = OracleTypes.varchar2Of(100).nullableOk();

Custom domain types​

Wrap base types with custom Java types using transform:

// Wrapper type
data class EmployeeId(val value: Long)

// Create OracleType from NUMBER
val empIdType: OracleType<EmployeeId> =
    OracleTypes.numberLong.transform(::EmployeeId, EmployeeId::value)

Required driver dependencies​

The core driver is com.oracle.database.jdbc:ojdbc11. A second artifact is needed when you bind OBJECT or VARRAY literals inline in SQL — e.g. TABLE(?) over a VARRAY parameter, or SELECT ?::my_t FROM dual:

com.oracle.database.xml:xdb:23.6.0.24.10

Without xdb on the runtime classpath, the first inline OBJECT/VARRAY bind fails with NoClassDefFoundError: oracle/xdb/XMLType. The driver itself compiles fine without it — the error only surfaces at execute time.

Reserved-word traps in OBJECT types​

Oracle's reserved-word list applies inside CREATE TYPE. Attribute names like LEVEL, ROWID, DATE, NUMBER, USER will compile the type but leave it in state INVALID:

CREATE TYPE skill_t AS OBJECT (name VARCHAR2(100), LEVEL NUMBER(3));
-- reports "Type created" but SELECT status FROM user_objects WHERE object_name='SKILL_T' → INVALID
-- every downstream use then fails with ORA-00902 / ORA-03050

Rename the attribute (LVL, ROW_ID, etc.) or quote the identifier. The error messages don't point back to the CREATE TYPE — they show up at SELECT/INSERT time.

DDL inside a transaction​

Oracle auto-commits DDL. Running several CREATE TYPE / CREATE TABLE statements inside a single tx.transact { conn -> ... } block can produce surprising catalog visibility issues — a subsequent CREATE TABLE referencing a freshly-declared TYPE may fail with ORA-00902 even though the type is valid. Split each DDL into its own tx.transact { } call to keep each statement's auto-commit boundary clean.

Schema-qualified type names​

When a connection's current schema owns the type, you can reference it bare (compositeOf("SKILL_T", ...)). Cross-schema usage generally requires the fully-qualified name (compositeOf("TYPR.SKILL_T", ...)). If in doubt, use the qualified form — it works from either side.