Oracle Subscribers

Microsoft® SQL Server™ 2000 includes an ODBC driver and OLE DB provider that support Oracle subscriptions to SQL Server publications on Intel computers. SQL Server 2000 Setup installs the driver automatically.

Note  To replicate to Oracle ODBC and OLE DB Subscribers, you must also obtain the appropriate Oracle SQL*Net driver from Oracle or from your software vendor. You must then install the driver on the Publisher and the Distributor.

Replication Restrictions for Oracle Subscribers

The following restrictions apply when replicating to an Oracle ODBC Subscriber:

• Replication of tables that have names with spaces will not be created on the Oracle subscriber. Replication will fail with Oracle error ORA-00903: invalid table name.
  
  
• The date data type is a small datetime (the range is 4712 B.C. to 4712 A.D.).

  If you are replicating to Oracle, verify that SQL Server datetime entries in a replicated column are within this range.

• A replicated table can have only one column of either text or image data type, which is mapped to long raw.
  
  
• The datetime data type is mapped to char4.
  
  
• The SQL Server 2000 ranges for float and real data types are different from the Oracle ranges.

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

Oracle Data Type Definitions

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