This topic describes the incompatibilities between an Oracle database and an Oracle tenant of OceanBase Database in changing the data types of columns that have different constraints.
Note
- In the following tables, the supported target types of columns in Oracle tenants of OceanBase Database are results of type mapping.
- In the following tables, a hyphen (-) indicates none and 'All' indicates all types.
Change the type of a PRIMARY KEY column
The following table describes the incompatibilities between an Oracle database and an Oracle tenant of OceanBase Database in changing the type of a PRIMARY KEY column by using the ALTER TABLE table_name MODIFY DDL statement.
| Column type in Oracle database | Supported target type | Column type in Oracle tenant of OceanBase Database | Supported target type |
|---|---|---|---|
| INTEGER | All | NUMBER(38,0) | - |
| INT | All | NUMBER(38,0) | - |
| SMALLINT | All | NUMBER(38,0) | - |
| BINARY_FLOAT | All | BINARY_FLOAT | - |
| BINARY_DOUBLE | All | BINARY_DOUBLE | - |
| DECIMAL | All | NUMBER | - |
| DEC | All | NUMBER | - |
| NUMERIC | All | NUMBER | - |
| REAL | All | NUMBER | - |
| FLOAT | All | FLOAT | - |
| NUMBER | All | NUMBER | - |
| NCHAR | All | NCHAR | - |
| CHAR | All | CHAR | - |
| CHARACTER | All | CHAR(n) | - |
| NVARCHAR2 | All | NVARCHAR2(size) | - |
| VARCHAR2 | All | VARCHAR2 | - |
| VARCHAR | All | VARCHAR2 | - |
| RAW | All | RAW | - |
| ROWID | All | ROWID | UROWID |
| UROWID | All | UROWID | ROWID |
| TIMESTAMP | All | TIMESTAMP | - |
| DATE | All | DATE | - |
| INTERVAL YEAR TO MONTH | All | INTERVAL YEAR TO MONTH | - |
| INTERVAL DAY TO SECOND | All | INTERVAL DAY TO SECOND | - |
Change the type of a UNIQUE KEY column
The incompatibilities between the Oracle database and the Oracle tenant of OceanBase Database in changing the type of a UNIQUE KEY column by using the ALTER TABLE table_name MODIFY DDL statement are the same as that in changing the type of a PRIMARY KEY column.
Change the type of a column with a CHECK constraint
The following table describes the incompatibilities between an Oracle database and an Oracle tenant of OceanBase Database in changing the type of a column that has a CHECK constraint by using the ALTER TABLE table_name MODIFY DDL statement.
| Column type in Oracle database | Supported target type | Column type in Oracle tenant of OceanBase Database | Supported target type |
|---|---|---|---|
| INTEGER | NUMERIC\NUMBER\DECIMAL\DEC\FLOAT\REAL\INT\SMALLINT | NUMBER(38,0) | - |
| INT | NUMERIC\NUMBER\DECIMAL\DEC\FLOAT\REAL\INTEGER\SMALLINT | NUMBER(38,0) | - |
| SMALLINT | NUMERIC\NUMBER\DECIMAL\DEC\FLOAT\REAL\INTEGER\INT | NUMBER(38,0) | - |
| BINARY_FLOAT | - | BINARY_FLOAT | - |
| BINARY_DOUBLE | - | BINARY_DOUBLE | - |
| DECIMAL | NUMERIC\NUMBER\DEC\FLOAT\REAL\INT\SMALLINT\INTEGER | NUMBER | - |
| DEC | NUMERIC\NUMBER\DECIMAL\FLOAT\REAL\INT\SMALLINT\INTEGER | NUMBER | - |
| NUMERIC | NUMBER\DEC\DECIMAL\FLOAT\REAL\INT\SMALLINT\INTEGER | NUMBER | - |
| REAL | NUMBER\DEC\DECIMAL\FLOAT\INT\SMALLINT\INTEGER | NUMBER | - |
| FLOAT | NUMERIC\NUMBER\DEC\DECIMAL\REAL\INT\SMALLINT\INTEGER | FLOAT | - |
| NUMBER | NUMERIC\DEC\DECIMAL\FLOAT\REAL\INT\SMALLINT\INTEGER | NUMBER | - |
| NCHAR | CHAR\CHARACTER\NVARCHAR2\VARCHAR2\VARCHAR | NCHAR | - |
| CHAR | NCHAR\CHARACTER\NVARCHAR2\VARCHAR2\VARCHAR | CHAR | - |
| CHARACTER | NCHAR\CHAR\NVARCHAR2\VARCHAR2\VARCHAR | CHAR | - |
| NVARCHAR2 | NCHAR\CHAR\CHARACTER\VARCHAR2\VARCHAR | NVARCHAR2 | - |
| VARCHAR2 | NCHAR\CHAR\CHARACTER\NVARCHAR2\VARCHAR | VARCHAR2 | - |
| VARCHAR | NCHAR\CHAR\CHARACTER\NVARCHAR2\VARCHAR2 | VARCHAR2 | - |
| RAW | - | RAW | - |
| ROWID | - | ROWID | UROWID |
| UROWID | - | UROWID | ROWID |
| TIMESTAMP | DATE | TIMESTAMP | - |
| TIMESTAMP WITH LOCAL TIME ZONE | - | TIMESTAMP WITH LOCAL TIME ZONE | - |
| DATE | TIMESTAMP | DATE | - |
| INTERVAL YEAR TO MONTH | - | INTERVAL YEAR TO MONTH | - |
| INTERVAL DAY TO SECOND | - | INTERVAL DAY TO SECOND | - |
Change the type of a FOREIGN KEY column
The following table describes the incompatibilities between an Oracle database and an Oracle tenant of OceanBase Database in changing the type of a FOREIGN KEY column by using the ALTER TABLE table_name MODIFY DDL statement.
| Column type in Oracle database | Supported target type | Column type in Oracle tenant of OceanBase Database | Supported target type |
|---|---|---|---|
| INTEGER | NUMERIC\NUMBER\DECIMAL\DEC\FLOAT\REAL\INT\SMALLINT | NUMBER(38,0) | - |
| INT | NUMERIC\NUMBER\DECIMAL\DEC\FLOAT\REAL\INTEGER\SMALLINT | NUMBER(38,0) | - |
| SMALLINT | NUMERIC\NUMBER\DECIMAL\DEC\FLOAT\REAL\INTEGER\INT | NUMBER(38,0) | - |
| BINARY_FLOAT | - | BINARY_FLOAT | - |
| BINARY_DOUBLE | - | BINARY_DOUBLE | - |
| DECIMAL | NUMERIC\NUMBER\DEC\FLOAT\REAL\INT\SMALLINT\INTEGER | NUMBER | - |
| DEC | NUMERIC\NUMBER\DECIMAL\FLOAT\REAL\INT\SMALLINT\INTEGER | NUMBER | - |
| NUMERIC | NUMBER\DEC\DECIMAL\FLOAT\REAL\INT\SMALLINT\INTEGER | NUMBER | - |
| REAL | NUMBER\DEC\DECIMAL\FLOAT\INT\SMALLINT\INTEGER | NUMBER | - |
| FLOAT | NUMERIC\NUMBER\DEC\DECIMAL\REAL\INT\SMALLINT\INTEGER | FLOAT | - |
| NUMBER | NUMERIC\DEC\DECIMAL\FLOAT\REAL\INT\SMALLINT\INTEGER | NUMBER | - |
| NCHAR | CHAR\CHARACTER | NCHAR | - |
| CHAR | NCHAR\CHARACTER | CHAR | - |
| CHARACTER | NCHAR\CHAR | CHAR | - |
| NVARCHAR2 | VARCHAR2\VARCHAR | NVARCHAR2 | - |
| VARCHAR2 | NVARCHAR2\VARCHAR | VARCHAR2 | - |
| VARCHAR | VARCHAR2 , NVARCHAR2 | VARCHAR2 | - |
| RAW | - | RAW | - |
| ROWID | - | ROWID | UROWID |
| UROWID | - | UROWID | ROWID |
| TIMESTAMP | - | TIMESTAMP | - |
| TIMESTAMP WITH LOCAL TIME ZONE | - | TIMESTAMP WITH LOCAL TIME ZONE | - |
| DATE | - | DATE | - |
| INTERVAL YEAR TO MONTH | - | INTERVAL YEAR TO MONTH | - |
| INTERVAL DAY TO SECOND | - | INTERVAL DAY TO SECOND | - |
