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

Notes

Please note the following when planning the replication.

1. MYSQL and SQL Server: When replicating to MySQL following conversion are applied:
   1. FLOAT and NUMBER datatypes are converted to DECIMAL datatype. NOTE: Due to MySQL limitation, the 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.
   2. NUMBER with 0 scale is converted to BIGINT. This is to allow for precise rounding behaviour. NOTE: if the precision of NUMBER is greater than 20 ( NUMBER(p,0); p > 20 ), the corresponding column datatype in APPLY.sql must be manually altered from BIGINT to DECIMAL(p,0), since BIGINT can't handle numbers > 18446744073709551615 (unsigned), and this might lead to a conflict in certain situations.
2. 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.
3. MySQL ONLY: When replicating to MySQL the target requires 5.6.4 MySQL or higher. Please note that the TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE datatypes are NOT supported directly in MySQL. They can be replicated across to TIMESTAMP in MySQL but, by default, the TZ component will not be honored. Manual conversion on the Oracle side to map to TIMESTAMP in MySQL may be possible using functions such as SYS_EXTRACT_UTC().
4. 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. 
5. 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.
6. 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.
7. SQL Server ONLY: Usage of Oracle NUMBER(*,38) datatype can cause overflow when replicating to DECIMAL due to storage specifics resulting in different limitations, allowing Oracle to actually store 39 digits in the NUMBER (*,38) column, while SQL Server handles only 38 digits in total.

Unsupported Datatypes and functionality

Unsupported data types are listed here: Unsupported Datatypes

 Unsupported functionality is listed here: Functionality Currently NOT Supported.