We're here to help!

Supported Datatypes

Follow

SQL query can be run to determine if there are any datatypes that will not be supported by Dbvisit Replicate.

Supported Datatypes Conversion Table

SourceTarget

Applicable Note №

(see below)

OracleOracle 9.2Oracle 10+MS SQL ServerMySQLTiberoPostgres
NUMBERNUMBERNUMBERDECIMAL or BIGINT(when scale==0)DECIMAL or BIGINT(when scale==0)NUMBERSMALLINT - NUMERIC1, 5, 6, 7, 9
FLOATFLOATFLOATDECIMALDECIMALFLOATNUMERIC1
VARCHAR2VARCHAR2VARCHAR2VARCHARVARCHARVARCHAR2VARCHAR
VARCHARVARCHARVARCHARVARCHARVARCHARVARCHARVARCHAR
CHARCHARCHARCHARCHARCHARCHAR4
NCHARNCHARNCHARCHARCHARNCHARCHAR4
DATEDATEDATEDATETIMEDATETIMEDATETIMESTAMP
RAWRAWRAWVARBINARYVARBINARYRAWBYTEA
LONGxLONGTEXTTEXTLONGTEXT
LONG RAWxLONG RAWVARBINARY(MAX)BLOBLONG RAWBYTEA2
BFILEBFILEBFILExxxx2
CLOBCLOBCLOBxxxx2
BLOBBLOBBLOBxxxx2
LOBLOBLOBxxxx2
NCLOBNCLOBNCLOBxxxx2
INTERVALINTERVALINTERVALxxxx
TIMESTAMPTIMESTAMPTIMESTAMPDATETIME2TIMESTAMPTIMESTAMPTIMESTAMP3
BINARY_FLOATxBINARY_FLOATxxxx
BINARY_DOUBLExBINARY_DOUBLExxxx
ROWIDxROWIDxxxx
XMLxXMLxxxx8

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.
  8. XML datatype is supported as CLOB storage.  Binary storage is not supported in this release.
  9. PostgreSQL ONLY: Number conversion rules
    1. NUMBER(p,0) with 1 <= p < 5 -> SMALLINT
    2. NUMBER(p,0) with 5 <= p < 9 -> INTEGER
    3. NUMBER(p,0) with 9 <= p < 19 -> BIGINT 
    4. NUMBER(p,0) fixed-point number with 19 <= p <= 38 -> NUMERIC(p)
    5. NUMBER(p,s) fixed-point number, s > 0 -> NUMERIC(p,s)  
    6. NUMBER  floating-point number ->  NUMERIC 
    7. 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.

Have more questions? Submit a request

Comments