Supported Datatypes
A SQL query can be run to determine if there are any datatypes that will not be supported by Dbvisit Replicate.
Supported Datatypes Conversion Table
| Source | Target | Applicable Note № (see below) | |||
|---|---|---|---|---|---|
| Oracle | Oracle 9.2 | Oracle 10+ | MS SQL Server | MySQL | |
| NUMBER | NUMBER | NUMBER | FLOAT or BIGINT(when scale==0) | DECIMAL or BIGINT(when scale==0) | 1, 5, 6 |
| FLOAT | FLOAT | FLOAT | DECIMAL | DECIMAL | 1 |
| VARCHAR2 | VARCHAR2 | VARCHAR2 | VARCHAR | VARCHAR | |
| VARCHAR | VARCHAR | VARCHAR | VARCHAR | VARCHAR | |
| CHAR | CHAR | CHAR | CHAR | CHAR | 4 |
| NCHAR | NCHAR | NCHAR | CHAR | CHAR | 4 |
| DATE | DATE | DATE | DATETIME | DATETIME | |
| RAW | RAW | RAW | VARBINARY | VARBINARY | |
| LONG | x | LONG | TEXT | TEXT | |
| LONG RAW | x | LONG RAW | VARBINARY(MAX) | BLOB | 2 |
| BFILE | BFILE | BFILE | x | x | 2 |
| CLOB | CLOB | CLOB | x | x | 2 |
| BLOB | BLOB | BLOB | x | x | 2 |
| LOB | LOB | LOB | x | x | 2 |
| NCLOB | NCLOB | NCLOB | x | x | 2 |
| INTERVAL | INTERVAL | INTERVAL | x | x | |
| TIMESTAMP | TIMESTAMP | TIMESTAMP | x | TIMESTAMP | 3 |
Notes
Please note the following when planning the replication.
- MYSQL and SQL Server: When replicating to MySQL FLOAT and NUMBER datatypes are converted to DECIMAL datatype, and NUMBER with 0 scale converted to BIGINT. This is to allow for precise rounding. There is a MySQL limitation - number of digits to the right of the decimal point (the scale) has a range of 0 to 30 and must be no larger than maximum number of digits (the precision). More details on this MySQL limitation can be found here. Due to this limitation, it is recommended to explicitly specify the NUMBER scale to be less than or equal to 30 in all cases when there is a non-negligible probability of NUMBER column containing more than 30 digits after decimal points in the source database table.
- A table cannot contain just one column of this type. Must have at least one column of "non-CLOB" type and the LOB, preferably the other column is a primary key.
- MySQL ONLY: When replicating to MySQL the target requires 5.6.4 MySQL or higher.
- MySQL ONLY: Maximum scale for CHAR datatype in MYSQL is 255. If the source column scale is higher than this, APPLY.sql should be manually altered to use BLOB or TEXT datatypes.
- MySQL and SQL Server: While NUMBER(p,s) datatype where p < s is handled correctly by Oracle, such dimensions cannot be applied to DECIMAL datatype (i.e. it is not possible to create a column of type DECIMAL(1,3)). APPLY.sql should be manually altered in such cases to address this situation, and in DECIMAL(M,D), maximum number of digits (M) should be amended to satisfy the following condition: M ≥ D.
- MySQL ONLY: When both precision and scale are specified for NUMBER(p,s) column (as in NUMBER(35,38)), and s > 30, APPLY.sql should be examined to decrease the scale manually to be less than or equal to 30.
Unsupported Datatypes and functionality
Unsupported data types are listed here: Unsupported Datatypes
Unsupported functionality is listed here: Functionality Currently NOT Supported.
- Database NLS settings:
The NLS_LANG variable is set at the apply environment and behaves like the NLS_LANG environment does: it sets the language, territory and character set of the apply session. The language and territory are not very important (they affect things like Oracle error messages shown), the character set must be set to the source database character set, so the any character strings applied are treated correctly according the character set they are in.
(NCHARSET) - Database national character set:
This is the national character set of the source database. This is relevant only for NCHAR/NVARCHAR2 columns.
Only UTF8 and AL16UTF16 values are supported; Oracle expects Unicode values to be specified in UTF16, so if the source database is using UTF8 (specified by this setting), apply does the necessary conversion to UTF16.