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 (user-defined types) are supported via generated code:

// Generated code creates OracleType for your OBJECT type
// Example for ADDRESS_T type:
OracleType<AddressT> addressType = AddressT.oracleType;

// Insert using the generated type
AddressT addr = new AddressT("123 Main St", "City", "12345");

Nested Tables and VARRAYs​

Oracle collection types are fully supported:

// Nested tables - generated as List<Element>
OracleType<List<String>> stringTable = // generated

// VARRAYs - generated as arrays
OracleType<String[]> stringVarray = // generated

Nullable Types​

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

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

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)