The following table provides you with an overview of 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 Data Type Details. 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 |
||
