Skip to main content

Oracle Type Support

Foundations JDBC provides comprehensive support for 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.number(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_(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.varchar2(100)
val charType: OracleType<String> = OracleTypes.char_(10)
val nvarcharType: OracleType<String> = OracleTypes.nvarchar2(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.raw(100)  // RAW(100)

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

Date/Time Types​

Oracle TypeJava TypeNotes
DATELocalDateTimeDate + time (second precision)
TIMESTAMPLocalDateTimeFractional seconds (default: 6)
TIMESTAMP WITH TIME ZONEOffsetDateTimeExplicit timezone
TIMESTAMP WITH LOCAL TIME ZONEInstantSession timezone
val dateType: OracleType<LocalDateTime> = OracleTypes.date
val tsType: OracleType<LocalDateTime> = OracleTypes.timestamp
val ts3: OracleType<LocalDateTime> = OracleTypes.timestamp(3)  // TIMESTAMP(3)
val tstz: OracleType<OffsetDateTime> = OracleTypes.timestampWithTimeZone
val tsltz: OracleType<Instant> = OracleTypes.timestampWithLocalTimeZone

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

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.varchar2(200), Address::street)
        .field("CITY", OracleTypes.varchar2(100), Address::city)
        .field("ZIP", OracleTypes.varchar2(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.varchar2(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.varchar2(100), OrderItem::productName)
            .field("QUANTITY", OracleTypes.numberAsInt(10), OrderItem::quantity)
            .field("UNIT_PRICE", OracleTypes.number(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.varchar2(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)