This topic describes the supported data type conversion pairs during data migration from an Oracle database to an Oracle tenant of OceanBase Database.
Supported data types
Oracle Database | Oracle tenant of OceanBase Database | Description |
INTEGER | NUMBER(38,0) | |
INT | NUMBER(38,0) | |
SMALLINT | NUMBER(38,0) | |
BINARY_FLOAT | BINARY_FLOAT | The floating-point numbers with binary precision. It is the data type of the floating-point numbers with 32-place single precision. This data type is consistent in Oracle databases and Oracle tenants of OceanBase Database. |
BINARY_DOUBLE | BINARY_DOUBLE | The floating-point numbers with binary precision. It is the data type of the floating-point numbers with 64-place dual precision. This data type is consistent in Oracle databases and Oracle tenants of OceanBase Database. |
DECIMAL(p,s) | NUMBER(p,s) | |
DEC(p,s) | NUMBER(p,s) | |
NUMERIC(p,s) | NUMBER(p,s) | |
REAL(p,s) | NUMBER(p,s) | |
FLOAT (p) | FLOAT (p) | The sub-type of the NUMBER data type. Binary precision range: [1,126]. FLOAT does not indicate floating-point numbers. This data type is consistent in Oracle databases and Oracle tenants of OceanBase Database. |
NUMBER[(p,s)] | NUMBER(p,s) | In Oracle tenants of OceanBase Database, this data type stores the variable-length fixed-point numbers with decimal precision or floating-point numbers. When it is the data type of floating-point numbers, the NUMBER data type does not include the p and s parameters. In Oracle databases, p ranges from 1 to 38, and s ranges from -84 to 127. |
NCHAR(size) | NCHAR(size) | The value range of the size parameter is [1, 2000]. The default and minimum values are 1. The storage size is twice the size bytes. |
CHAR (size [BYTE | CHAR]) | CHAR (size [BYTE | CHAR]) | The value range of the size parameter is [1, 2000]. The default and minimum values are 1. The storage size is size bytes or characters. |
CHARACTER(n) | CHAR(n) | |
NVARCHAR2(size) | NVARCHAR2(size) | The value range of the size parameter is [1, 32767]. The byte storage size is twice the number of characters entered. |
VARCHAR2(size [BYTE | CHAR]) | VARCHAR2(size [BYTE | CHAR]) | The value range of the size parameter is [1, 32767]. The storage size is the actual length of the bytes or characters entered, rather than size bytes or characters. |
VARCHAR(size) | VARCHAR2(size) | |
RAW(size) | RAW(size) | In Oracle tenants of OceanBase Database, this data type stores data in bytes. The maximum length of a RAW column is 2,000 bytes. A RAW PL variable can hold up to 32,767 bytes of data. In Oracle databases, a RAW database column can hold up to 2,000 bytes of data. |
LONG | CLOB | In Oracle tenants of OceanBase Database, a CLOB stores up to 48 MB of data. In Oracle databases, the maximum length of a LONG value is up to 2^31 - 1 bytes or 2 gigabytes. |
LONG RAW | BLOB | In Oracle tenants of OceanBase Database, a BLOB stores up to 48 MB of binary data. In Oracle databases, the maximum length of a LONG RAW value is up to 2^31 - 1 bytes or 2 gigabytes. |
ROWID | ROWID | The unique address of a row in the table, which is represented as a Base64 string. |
UROWID | UROWID | The logical address of a row in an index-organized table, which is represented as a Base64 string. |
TIMESTAMP (fractional_seconds_precision) | TIMESTAMP (fractional_seconds_precision) | In Oracle tenants of OceanBase Database, the precision and range are determined by the value of the scale parameter. Value range: [0,9]. Default value: 6. The minimum value 0 indicates that the timestamp is accurate to seconds, the maximum value 9 indicates that the timestamp is accurate to nanoseconds, and the default value 6 indicates that the timestamp is accurate to microseconds. In Oracle databases, the size is 7 or 11 bytes, depending on the precision. |
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE | TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE | In Oracle tenants of OceanBase Database, the precision and range are determined by the value of the scale parameter. Value range: [0,9]. Default value: 6. The minimum value 0 indicates that the timestamp is accurate to seconds, the maximum value 9 indicates that the timestamp is accurate to nanoseconds, and the default value 6 indicates that the timestamp is accurate to microseconds. In Oracle databases, the size is fixed at 13 bytes. |
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE | TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE | |
DATE | DATE | In Oracle tenants of OceanBase Database, the value range is [0001-01-01 00:00:00, 9999-12-31 23:59:59]. In Oracle databases, the valid date range is [January 1, 4712 BC, December 31, 9999 AD]. The size is fixed at 7 bytes. |
INTERVAL YEAR [(year_precision)] TO MONTH | INTERVAL YEAR [(year_precision)] TO MONTH | 5 bytes |
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)] | INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)] | 11 bytes |
BLOB | BLOB | |
CLOB | CLOB | |
NCLOB | CLOB |
Unsupported data types
Oracle Database | Oracle tenant of OceanBase Database |
TIMESTAMP_UNCONSTRAINED | - |
TIMESTAMP_TZ_UNCONSTRAINED | - |
TIMESTAMP_LTZ_UNCONSTRAINED | - |
YMINTERVAL_UNCONSTRAINED | - |
DSINTERVAL_UNCONSTRAINED | - |
YEAR | - |
MONTH | - |
DAY | - |
HOUR | - |
MINUTE | - |
SECOND | - |
TIMEZONE_HOUR | - |
TIMEZONE_MINUTE | - |
TIMEZONE_REGION | - |
TIMEZONE_ABBR | - |