How to get details on the requisite data type mapping between the source and target database.
With ever-growing number of open targets and Oracle and non-Oracle sources supported in SharePlex replication, there is a need for a command that can provide an easy reference to the data type mapping between the source and target combination of these database types.
The typemap command issued thru sp_ctrl comes in handy for this purpose.
The most commonly used option for typemap command is:
sp_ctrl>typemap show [sourcedatabase-targetdatabase] where sourcedatabase and targetdatabase refer to permissible source and target databases and the resulting output shows the source and target data type mapping for the columns on source and target respectively. It should be noted that not all data type that exist on source database could be replicated to the target. The following example shows the data type mapping between Oracle and SQL Server:
sp_ctrl (alvsupl14:9241)> typemap show oracle-sqlserver
Data type map from oracle to sqlserver
Source Target Notes
----------------------------------- ----------------------------------- -----
CHAR(n) CHAR(n)
VARCHAR2(n) VARCHAR(n)
NCHAR(n) NCHAR(n)
NVARCHAR2(n) NVARCHAR(n)
RAW(n) VARBINARY(n)
BLOB VARBINARY(MAX)
CLOB VARCHAR(MAX)
NCLOB NVARCHAR(MAX)
DATE DATETIME2(7)
TIMESTAMP(8:9) DATETIME2(7)
TIMESTAMP(p) DATETIME2(p)
TIMESTAMP(8:9) WITH TIME ZONE DATETIMEOFFSET(7)
TIMESTAMP(p) WITH TIME ZONE DATETIMEOFFSET(p)
LONG VARCHAR(MAX)
LONGRAW VARBINARY(MAX)
NUMBER FLOAT(53)
NUMBER(p,0:) NUMERIC(p,s)
NUMBER(p,s) FLOAT(53)
CHAR(n CHAR) CHAR(n)
VARCHAR2(n CHAR) VARCHAR(n)
BINARY_FLOAT FLOAT(24)
BINARY_DOUBLE FLOAT(53)
ANYDATA VARIENT
The users will typically be interested in the data type mapping for the database platform used by him/her.
If one were interested in data type mapping of all possible combinations of supported source/target, the command can be issued at the sp_ctrl prompt as “typemap show”. This will list out all possible combinations of data types for the various permissible sources and the respective targets that they can replicate to. The resulting output will keep growing as the # of supported sources and the respective number of supported open targets grow. For example, as of the creation of this article, there are 2 type of source databases supported (Oracle and SQL Server) and there are 8 type of target databases that are supported. The command will print tables that list the permissible combination of the data types on source and target for each of such combination. With 2 possible sources and 8 possible targets, you will see 13 matrices as seen below and not 16 (2 sources times 8 targets) as not all sources are supported for all targets. For example, as of version 9.2, SharePlex do not support replication from SQL Server to Hana, Sybase or Teradata. It should also be noted that the list will grow as you have more combinations available over a period of time.
Here is the output that lists out all supported combination:
sp_ctrl (alvsupl14:9241)> typemap show
Data type map from oracle to hana
Source Target Notes
----------------------------------- ----------------------------------- -----
CHAR(n) CHAR(n)
VARCHAR2(n) VARCHAR(n)
NCHAR(n) NCHAR(n)
NVARCHAR2(n) NVARCHAR(n)
RAW(n) VARBINARY(n)
BLOB BLOB
CLOB CLOB
NCLOB NCLOB
DATE TIMESTAMP
TIMESTAMP(p) TIMESTAMP
LONG CLOB
LONGRAW BLOB
NUMBER DECIMAL
NUMBER(p,0:) DECIMAL(p,s)
NUMBER(p,s) DECIMAL
BINARY_FLOAT DOUBLE
BINARY_DOUBLE DOUBLE
Data type map from oracle to mysql
Source Target Notes
----------------------------------- ----------------------------------- -----
CHAR(n) CHAR(n)
VARCHAR2(n) VARCHAR(n)
RAW(n) VARBINARY(n)
BLOB LONGBLOB
CLOB LONGTEXT
DATE DATETIME(0)
TIMESTAMP(7:9) DATETIME(6)
TIMESTAMP(p) DATETIME(p)
LONG LONGTEXT
LONGRAW LONGBLOB
NUMBER DOUBLE
NUMBER(p,0:) NUMERIC(p,s)
NUMBER(p,s) DOUBLE
BINARY_FLOAT DOUBLE
BINARY_DOUBLE DOUBLE
Data type map from oracle to oracle
Source Target Notes
----------------------------------- ----------------------------------- -----
CHAR(n) CHAR(n)
VARCHAR2(n) VARCHAR2(n)
NCHAR(n) NCHAR(n)
NVARCHAR2(n) NVARCHAR2(n)
RAW(n) RAW(n)
BLOB BLOB
CLOB CLOB
NCLOB NCLOB
DATE DATE
TIMESTAMP(p) TIMESTAMP(p)
TIMESTAMP(p) WITH TIME ZONE TIMESTAMP(p) WITH TIME ZONE
TIMESTAMP(p) WITH LOCAL TIME ZONE TIMESTAMP(p) WITH LOCAL TIME ZONE
LONG LONG
LONGRAW LONGRAW
NUMBER NUMBER
BINARY_FLOAT BINARY_FLOAT
BINARY_DOUBLE BINARY_DOUBLE
ANYDATA ANYDATA
Data type map from oracle to postgres
Source Target Notes
----------------------------------- ----------------------------------- -----
CHAR(n) CHAR(n)
VARCHAR2(n) VARCHAR(n)
NCHAR(n) CHAR(n)
NVARCHAR2(n) VARCHAR(n)
CLOB TEXT
NCLOB TEXT
DATE DATE
TIMESTAMP(7:9) TIMESTAMP(6)
TIMESTAMP(p) TIMESTAMP(p)
TIMESTAMP(7:9) WITH TIME ZONE TIMESTAMP(6) WITH TIME ZONE
TIMESTAMP(p) WITH TIME ZONE TIMESTAMP(p) WITH TIME ZONE
TIMESTAMP(7:9) WITH LOCAL TIME ZONE TIMESTAMP(6)
TIMESTAMP(p) WITH LOCAL TIME ZONE TIMESTAMP(p)
LONG TEXT
NUMBER NUMERIC
NUMBER(p,0:) NUMERIC(p,s)
NUMBER(p,s) NUMERIC
CHAR(n CHAR) CHAR(n)
VARCHAR2(n CHAR) VARCHAR(n)
BINARY_FLOAT REAL
BINARY_DOUBLE DOUBLE PRECISION
Data type map from oracle to sqlserver
Source Target Notes
----------------------------------- ----------------------------------- -----
CHAR(n) CHAR(n)
VARCHAR2(n) VARCHAR(n)
NCHAR(n) NCHAR(n)
NVARCHAR2(n) NVARCHAR(n)
RAW(n) VARBINARY(n)
BLOB VARBINARY(MAX)
CLOB VARCHAR(MAX)
NCLOB NVARCHAR(MAX)
DATE DATETIME2(7)
TIMESTAMP(8:9) DATETIME2(7)
TIMESTAMP(p) DATETIME2(p)
TIMESTAMP(8:9) WITH TIME ZONE DATETIMEOFFSET(7)
TIMESTAMP(p) WITH TIME ZONE DATETIMEOFFSET(p)
LONG VARCHAR(MAX)
LONGRAW VARBINARY(MAX)
NUMBER FLOAT(53)
NUMBER(p,0:) NUMERIC(p,s)
NUMBER(p,s) FLOAT(53)
CHAR(n CHAR) CHAR(n)
VARCHAR2(n CHAR) VARCHAR(n)
BINARY_FLOAT FLOAT(24)
BINARY_DOUBLE FLOAT(53)
ANYDATA VARIENT
Data type map from oracle to sybase
Source Target Notes
----------------------------------- ----------------------------------- -----
CHAR(n) CHAR(n)
VARCHAR2(n) VARCHAR(n)
RAW(n) VARBINARY(n)
BLOB IMAGE
CLOB TEXT
DATE DATETIME(p)
LONG TEXT
LONGRAW IMAGE
NUMBER NUMERIC
NUMBER(p,0:) NUMERIC(p,s)
NUMBER(p,s) NUMERIC
BINARY_FLOAT FLOAT
BINARY_DOUBLE FLOAT
Data type map from oracle to teradata
Source Target Notes
----------------------------------- ----------------------------------- -----
CHAR(n) CHAR(n)
VARCHAR2(n) VARCHAR(n)
DATE TIMESTAMP(0)
NUMBER NUMBER
NUMBER(p,0:) NUMBER(p,s)
NUMBER(p,s) NUMBER
Data type map from oracle to tibero
Source Target Notes
----------------------------------- ----------------------------------- -----
CHAR(n) CHAR(n)
VARCHAR2(n) VARCHAR2(n)
NCHAR(n) NCHAR(n)
NVARCHAR2(n) NVARCHAR(n)
RAW(n) RAW(n)
BLOB BLOB
CLOB CLOB
NCLOB NCLOB
DATE DATE
TIMESTAMP(p) TIMESTAMP(p)
TIMESTAMP(p) WITH TIME ZONE TIMESTAMP(p) WITH TIME ZONE
TIMESTAMP(p) WITH LOCAL TIME ZONE TIMESTAMP(p) WITH LOCAL TIME ZONE
LONG LONG
LONGRAW LONG RAW
NUMBER NUMBER
NUMBER(p,s) NUMBER(p,s)
ROWID ROWID
BINARY_FLOAT BINARY_FLOAT
BINARY_DOUBLE BINARY_DOUBLE
INTERVAL YEAR TO MONTH INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND INTERVAL DAY TO SECOND
Data type map from sqlserver to mysql
Source Target Notes
----------------------------------- ----------------------------------- -----
CHAR(1:255) CHAR(n)
CHAR(256:) LONGTEXT
VARCHAR(n) VARCHAR(n)
NCHAR(1:255) CHAR(n)
NCHAR(256:) LONGTEXT
NVARCHAR(n) VARCHAR(n)
BINARY(1:255) BINARY(n)
BINARY(256:) LONGBLOB
VARBINARY(1:255) VARBINARY(n)
VARBINARY(256:) LONGBLOB
IMAGE LONGBLOB
TEXT LONGTEXT
NTEXT LONGTEXT
BIGINT BIGINT
INTEGER INTEGER
SMALLINT SMALLINT
TINYINT TINYINT
BIT TINYINT
MONEY NUMERIC(20,4)
NUMERIC(p,s) NUMERIC(p,s)
FLOAT(n) FLOAT
DATE DATE
TIME(p) TIME(p)
DATETIME DATETIME(0)
DATETIME2(p) DATETIME(p)
SMALLDATETIME DATETIME(0)
VARCHAR(MAX) LONGTEXT
NVARCHAR(MAX) LONGTEXT
VARBINARY(MAX) LONGBLOB
UNIQUEIDENTIFIER CHAR(36)
REAL FLOAT
Data type map from sqlserver to oracle
Source Target Notes
----------------------------------- ----------------------------------- -----
CHAR(1:3999) CHAR(n)
CHAR(4000:) CLOB
VARCHAR(1:3999) VARCHAR2(n)
VARCHAR(4000:) CLOB
NCHAR(1:3999) NCHAR(n)
NCHAR(4000:) NCLOB
NVARCHAR(1:3999) NVARCHAR2(n)
NVARCHAR(4000:) NCLOB
BINARY(n) RAW(n)
VARBINARY(n) RAW(n)
IMAGE BLOB
TEXT CLOB
NTEXT NCLOB
BIGINT NUMBER
INTEGER NUMBER
SMALLINT NUMBER
TINYINT NUMBER
BIT NUMBER(1,s)
MONEY NUMBER
NUMERIC(p,s) NUMBER(p,s)
FLOAT(n) NUMBER
DATE DATE
TIME(p) DATE
DATETIME TIMESTAMP(3)
DATETIME2(p) TIMESTAMP(p)
DATETIMEOFFSET(p) TIMESTAMP(p) WITH TIME ZONE
SMALLDATETIME DATE
VARCHAR(MAX) CLOB
NVARCHAR(MAX) NCLOB
VARBINARY(MAX) BLOB
UNIQUEIDENTIFIER CHAR(36)
REAL NUMBER
Data type map from sqlserver to postgres
Source Target Notes
----------------------------------- ----------------------------------- -----
CHAR(n) CHAR(n)
VARCHAR(n) VARCHAR(n)
NCHAR(n) CHAR(n)
NVARCHAR(n) VARCHAR(n)
TEXT TEXT
NTEXT TEXT
BIGINT NUMERIC
INTEGER INTEGER
SMALLINT SMALLINT
TINYINT SMALLINT
BIT BOOLEAN
MONEY NUMERIC(20,4)
NUMERIC(p,s) NUMERIC(p,s)
FLOAT(25:) DOUBLE PRECISION
FLOAT(1:24) REAL
DATE DATE
TIME(p) TIME(p)
DATETIME TIMESTAMP(3)
DATETIME2(p) TIMESTAMP(p)
DATETIMEOFFSET(p) TIMESTAMP(p) WITH TIME ZONE
SMALLDATETIME TIMESTAMP(0)
VARCHAR(MAX) TEXT
NVARCHAR(MAX) TEXT
UNIQUEIDENTIFIER CHAR(36)
REAL REAL
Data type map from sqlserver to sqlserver
Source Target Notes
----------------------------------- ----------------------------------- -----
CHAR(n) CHAR(n)
VARCHAR(n) VARCHAR(n)
NCHAR(n) NCHAR(n)
NVARCHAR(n) NVARCHAR(n)
BINARY(n) BINARY(n)
VARBINARY(n) VARBINARY(n)
IMAGE IMAGE
TEXT TEXT
NTEXT NTEXT
BIGINT BIGINT
INTEGER INTEGER
SMALLINT SMALLINT
TINYINT TINYINT
BIT BIT
MONEY MONEY
NUMERIC(p,s) NUMERIC(p,s)
FLOAT(p) FLOAT(p)
DATE DATE
TIME(p) TIME(p)
DATETIME DATETIME
DATETIME2(p) DATETIME2(p)
DATETIMEOFFSET(p) DATETIMEOFFSET(p)
SMALLDATETIME SMALLDATETIME
UNIQUEIDENTIFIER UNIQUEIDENTIFIER
REAL REAL
Data type map from sqlserver to tibero
Source Target Notes
----------------------------------- ----------------------------------- -----
CHAR(1:3999) CHAR(n)
CHAR(4000:) CLOB
VARCHAR(1:3999) VARCHAR2(n)
VARCHAR(4000:) CLOB
NCHAR(1:3999) NCHAR(n)
NCHAR(4000:) NCLOB
NVARCHAR(1:3999) NVARCHAR(n)
NVARCHAR(4000:) NCLOB
BINARY(n) RAW(n)
VARBINARY(n) RAW(n)
IMAGE BLOB
TEXT CLOB
NTEXT NCLOB
BIGINT NUMBER
INTEGER NUMBER
SMALLINT NUMBER
TINYINT NUMBER
BIT NUMBER(1,s)
MONEY NUMBER
NUMERIC(p,s) NUMBER(p,s)
FLOAT(n) NUMBER
DATE DATE
TIME(p) DATE
DATETIME TIMESTAMP(3)
DATETIME2(p) TIMESTAMP(p)
DATETIMEOFFSET(p) TIMESTAMP(p) WITH TIME ZONE
SMALLDATETIME DATE
VARCHAR(MAX) CLOB
NVARCHAR(MAX) NCLOB
VARBINARY(MAX) BLOB
UNIQUEIDENTIFIER CHAR(36)
REAL NUMBER
