SQL Server Replication Data Type & Oracle Data Type

Oracle Data Type Definitions

1. The following table lists the Oracle data type definitions.

Oracle data type	Definition
CHAR	<=2000
DATE	Jan 1, 4712 B.C. to Dec 31, 4712 A.D.
DECIMAL	Same as Number
FLOAT	Same as Number
INTEGER	Same as Number
LONG	<=2GB
LONG RAW	Raw data; Same as Long
LONG VARCHAR	Same as Long
NUMBER	1.0E-130 to 9.99..E125
SMALLINT	Same as Number
RAW	Raw Binary Data <=255 bytes
ROWID	Unique Value
VARCHAR2	<=4000 bytes
VARCHAR	Same as Varchar2
BLOB	Binary Large Object <=4GB
COB	Char Large Object <=4GB
NCLOB	Same as Clob (for multibyte)
BFILE	Pointer to binary operating file

2. The following table maps data types for replication to Oracle Subscribers.

SQL Server 2000 data type	Oracle data type
bigint	NUMBER
binary	LONG RAW NOT NULL
bit	NUMBER (1, 0)
char	VARCHAR2 (900) NOT NULL
datetime	DATE
decimal	NUMBER (255, 3) NOT NULL
float	FLOAT NOT NULL
image	LONG RAW
int	NUMBER (255, 3) NOT NULL
money	NUMBER (255, 3) NOT NULL
nchar	VARCHAR2 (2000) NOT NULL
ntext	LONG
numeric	NUMBER (255, 3) NOT NULL
nvarchar	VARCHAR2 (2000) NOT NULL
real	FLOAT NOT NULL
smallint	NUMBER (255, 3) NOT NULL
smalldatetime	DATE NOT NULL
smallmoney	NUMBER (255, 3) NOT NULL
sql_variant	LONG
sysname	CHAR(255)
text	LONG
timestamp	RAW (255)
tinyint	NUMBER (255, 3) NOT NULL

3. The following table shows the data type mappings that are used when data is replicated to a Subscriber running Oracle.

SQL Server 2005 Data Type	Oracle Data Type
BIGINT	NUMBER(19,0)
BINARY(1-2000)	RAW(1-2000)
BINARY(2001-8000)	BLOB
BIT	NUMBER(1)
CHAR(1-2000)	CHAR(1-2000)
CHAR(2001-4000)	VARCHAR2(2001-4000)
CHAR(4001-8000)	CLOB
DATETIME	DATE
DECIMAL(1-38, 0-38)	NUMBER(1-38, 0-38)
DOUBLE PRECISION	FLOAT
FLOAT	FLOAT
IMAGE	BLOB
INT	NUMBER(10,0)
MONEY	NUMBER(19,4)
NCHAR(1-1000)	CHAR(1-1000)
NCHAR(1001-4000)	NCLOB
NTEXT	NCLOB
NUMERIC(1-38, 0-38)	NUMBER(1-38, 0-38)
NVARCHAR(1-1000)	VARCHAR2(1-2000)
NVARCHAR(1001-4000)	NCLOB
NVARCHAR(MAX)	NCLOB
REAL	REAL
SMALLDATETIME	DATE
SMALLINT	NUMBER(5,0)
SMALLMONEY	NUMBER(10,4)
SQL_VARIANT	N/A
SYSNAME	VARCHAR2(128)
TEXT	CLOB
TIMESTAMP	RAW(8)
TINYINT	NUMBER(3,0)
UNIQUEIDENTIFIER	CHAR(38)
VARBINARY(1-2000)	RAW(1-2000)
VARBINARY(2001-8000)	BLOB
VARCHAR(1-4000)	VARCHAR2(1-4000)
VARCHAR(4001-8000)	CLOB
VARBINARY(MAX)	BLOB
VARCHAR(MAX)	CLOB
XML	NCLOB