Data Type Mapping

This table describes the data type mapping between Oracle and Exasol.

Oracle Exasol Comment
Numeric Data Types

NUMBER(p, s)

DECIMAL(p, s)

Maximum precision in Oracle is 38, while in Exasol it's 36

DECIMAL(p, s)

DEC(p, s)

INTEGER

DECIMAL(36) or BIGINT

INT

SMALLINT

DOUBLE PRECISION

DOUBLE PRECISION

Possible loss in precision and scale

FLOAT

REAL

BINARY_FLOAT

BINARY_DOUBLE

Character Data Types

CHAR(n [BYTE | CHAR])

CHAR(n [CHAR])

By default Exasol stores strings in UTF-8 and the length is always defined by characters, not bytes. Note that a single character can consume up to 4 bytes in Oracle.

VARCHAR2(n [BYTE | CHAR])

VARCHAR(n [CHAR])

NCHAR(n)

CHAR(n)

 

NVARCHAR2(n)

VARCHAR(n)

 

CLOB

VARCHAR(n), if string length <= 2.000.000

If the maximum length of the CLOB / NCLOB field, n, is less than 2,000,000 characters, it fits into a VARCHAR(n) field. A CLOB / NCLOB field can hold between 8TB and 128TB of data.

For large data fields, it is recommended to store the data outside of Exasol.

NCLOB

JSON (Oracle 21)

JSON data can be stored in VARCHAR(n) fields if the length of the JSON string is less than 2,000,000 characters. You can use JSON Functions to retrieve and transform JSON inputs.
DATETIME Data Types

DATE

DATE, if time is not stored

TIMSTAMP, if time is stored

 

TIMESTAMP(p)

TIMESTAMP

Timestamp accuracy is limited to milliseconds, precisions beyond that will be truncated.

TIMESTAMP(p) WITH LOCAL TIME ZONE

TIMESTAMP

We recommend normalizing the timestamps to UTC in Exasol. Exasol also offers a TIMESTAMP WITH LOCAL TIME ZONE datatype, which interprets the input/output values (for example, while inserting data or querying the table) depending on the session time zone.

Be aware that this is different from Oracle’s behavior, where each value stored may have a different time zone

TIMESTAMP(p) WITH TIME ZONE

TIMESTAMP

INTERVAL YEAR (p) TO MONTH

INTERVAL YEAR(p) TO MONTH

 

INTERVAL DAY(pd) TO SECOND(ps)

INTERVAL DAY(pd) TO SECOND(ps)

The accuracy is limited to milliseconds, precisions beyond that will be truncated
LONG and RAW Data Types

LONG

VARCHAR(n), if string length <= 2.000.000

Oracle recommends not to create tables with LONG columns, instead it recommends using a CLOB / NCLOB field. However, if the maximum length of the LONG field, n, is less than 2.000.000 characters, it can be migrated into VARCHAR(n) field in Exasol. A LONG field can contain a character string up to 2GB.

For large data fields, it is recommended to store the data outside of Exasol.

RAW(n)

HASHTYPE(n), if 1 <= n <= 1024

Binary data types are not supported in Exasol. However, there is a data type in Exasol introduced specifically to store hexadecimal hash values up to 1024 byte, the HASHTYPE data type. If the size of the RAW field, n, is used for hash values less than 1024 BYTES and is converted to hexadecimal representation, it can be migrated to HASHTYPE(n) in Exasol.
Unsupported Data Types

LONG RAW

Not supported

Binary data types are not supported.

BLOB

BFILE

ROWID

Not supported

Migration not needed.

Exasol has the pseudo column ROWID as well.

UROWID