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
Applicable Note №
|Oracle||Oracle 9.2||Oracle 10+||MS SQL Server||MySQL||Tibero||Postgres|
|NUMBER||NUMBER||NUMBER||DECIMAL or BIGINT(when scale==0)||DECIMAL or BIGINT(when scale==0)||NUMBER||SMALLINT - NUMERIC||1, 5, 6, 7, 9|
|LONG RAW||x||LONG RAW||VARBINARY(MAX)||BLOB||LONG RAW||BYTEA||2|
Please note the following when planning the replication.
- MYSQL and SQL Server: When replicating to MySQL following conversion are applied:
- 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.
- 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.
- 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. 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().
- 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.
- 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.
- XML datatype is supported as CLOB storage. Binary storage is not supported in this release.
- PostgreSQL ONLY: Number conversion rules
- NUMBER(p,0) with 1 <= p < 5 -> SMALLINT
- NUMBER(p,0) with 5 <= p < 9 -> INTEGER
- NUMBER(p,0) with 9 <= p < 19 -> BIGINT
- NUMBER(p,0) fixed-point number with 19 <= p <= 38 -> NUMERIC(p)
- NUMBER(p,s) fixed-point number, s > 0 -> NUMERIC(p,s)
- NUMBER floating-point number -> NUMERIC
- NUMERIC(p,s) -> NUMERIC(p,s)
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.