How to synchronize data in SQLServer2005 to Oracle

Sometimes, because of project development, some tables in the SQLServer2005 must be synchronized to the Oracle database, which can be read by other systems. Data synchronization between different database types can be implemented using linked servers and SQLAgent. Suppose our side (SQLServer2005) has a contract management system, where the table contract and Contract_project are required to sync to an MIS system (oracle9i) then we can follow the following steps to implement the database synchronization.

1. Create the corresponding contract and Contract_project tables in Oracle, which fields we need to synchronize to build those fields into the Oracle table.

Note that Oracle data types and SQL Server data types are different, so what kind of relationship do they have? We can run under SQL Server:

SELECT *
FROM msdb.dbo.MSdatatype_mappings
SELECT *
FROM msdb.dbo.sysdatatypemappings

To view the data type correspondence between SQL Server and other database systems. The first SQL statement looks at the type correspondence of the SQL Oracle, and the second table shows the type correspondence of each database system in more detail. Based on the first table and the field types in our SQL Server, we can build our Oracle tables.

SQL Server and Oracle correspondence

  
 ORACLE  bigint  NUMBER  19  3  1
  ORACLE  binary  BLOB  NULL  0  1
  ORACLE  binary  RAW  -1  4  1
  ORACLE  bit  NUMBER  1  3  1
  ORACLE  char  CHAR  -1  4  1
  ORACLE  char  CLOB  NULL  0  1
  ORACLE  char  VARCHAR2  -1  4  1
  ORACLE  datetime  DATE  NULL  0  1
  ORACLE  decimal  NUMBER  -1  3  1
  ORACLE  doubleprecision  FLOAT  NULL  0  1
  ORACLE  float  FLOAT  NULL  0  1
  ORACLE  image  BLOB  NULL  0  1
  ORACLE  int  NUMBER  10  3  1
  ORACLE  money  NUMBER  19  3  1
  ORACLE  nchar  NCHAR  -1  4  1
  ORACLE  nchar  NCLOB  NULL  0  1
  ORACLE  ntext  NCLOB  NULL  0  1
  ORACLE  numeric  NUMBER  -1  3  1
  ORACLE  nvarchar  NCLOB  NULL  0  1
  ORACLE  nvarchar  NVARCHAR2  -1  4  1
  ORACLE  nvarchar(max)  NCLOB  NULL  0  1
  ORACLE  real  REAL  NULL  0  1
  ORACLE  smalldatetime  DATE  NULL  0  1
  ORACLE  smallint  NUMBER  5  3  1
  ORACLE  smallmoney  NUMBER  10  3  1
  ORACLE  sysname  NVARCHAR2  128  4  1
  ORACLE  text  CLOB  NULL  0  1
  ORACLE  timestamp  RAW  8  4  1
  ORACLE  tinyint  NUMBER  3  3  1
  ORACLE  uniqueidentifier  CHAR  38  4  1
  ORACLE  varbinary  BLOB  NULL  0  1
  ORACLE  varbinary  RAW  -1  4  1
  ORACLE  varbinary(max)  BLOB  NULL  0  1
  ORACLE  varchar  CLOB  NULL  0  1
  ORACLE  varchar  VARCHAR2  -1  4  1
  ORACLE  varchar(max)  CLOB  NULL  0  1
  ORACLE  xml  NCLOB  NULL  0  1
  ORACLE  bigint  NUMBER  19  3  1
  ORACLE  binary  BLOB  NULL  0  1
  ORACLE  binary  RAW  -1  4  1
  ORACLE  bit  NUMBER  1  3  1
  ORACLE  char  CHAR  -1  4  1
  ORACLE  char  CLOB  NULL  0  1
  ORACLE  char  VARCHAR2  -1  4  1
  ORACLE  datetime  DATE  NULL  0  1
  ORACLE  decimal  NUMBER  -1  3  1
  ORACLE  doubleprecision  FLOAT  NULL  0  1
  ORACLE  float  FLOAT  NULL  0  1
  ORACLE  image  BLOB  NULL  0  1
  ORACLE  int  NUMBER  10  3  1
  ORACLE  money  NUMBER  19  3  1
  ORACLE  nchar  CHAR  -1  4  1
  ORACLE  nchar  CLOB  NULL  0  1
  ORACLE  ntext  CLOB  NULL  0  1
  ORACLE  numeric  NUMBER  -1  3  1
  ORACLE  nvarchar  CLOB  NULL  0  1
  ORACLE  nvarchar  VARCHAR2  -1  4  1
  ORACLE  nvarchar(max)  CLOB  NULL  0  1
  ORACLE  real  REAL  NULL  0  1
  ORACLE  smalldatetime  DATE  NULL  0  1
  ORACLE  smallint  NUMBER  5  3  1
  ORACLE  smallmoney  NUMBER  10  3  1
  ORACLE  sysname  VARCHAR2  128  4  1
  ORACLE  text  CLOB  NULL  0  1
  ORACLE  timestamp  RAW  8  4  1
  ORACLE  tinyint  NUMBER  3  3  1
  ORACLE  uniqueidentifier  CHAR  38  4  1
  ORACLE  varbinary  BLOB  NULL  0  1
  ORACLE  varbinary  RAW  -1  4  1
  ORACLE  varbinary(max)  BLOB  NULL  0  1
  ORACLE  varchar  CLOB  NULL  0  1
  ORACLE  varchar  VARCHAR2  -1  4  1
  ORACLE  varchar(max)  CLOB  NULL  0  1
  ORACLE  xml  CLOB  NULL  0  1
  ORACLE  bigint  NUMBER  19  3  1
  ORACLE  binary  BLOB  NULL  0  1
  ORACLE  binary  RAW  -1  4  1
  ORACLE  bit  NUMBER  1  3  1
  ORACLE  char  CHAR  -1  4  1
  ORACLE  char  CLOB  NULL  0  1
  ORACLE  char  VARCHAR2  -1  4  1
  ORACLE  datetime  DATE  NULL  0  1
  ORACLE  decimal  NUMBER  -1  3  1
  ORACLE  doubleprecision  FLOAT  NULL  0  1
  ORACLE  float  FLOAT  NULL  0  1
  ORACLE  image  BLOB  NULL  0  1
  ORACLE  int  NUMBER  10  3  1
  ORACLE  money  NUMBER  19  3  1
  ORACLE  nchar  NCHAR  -1  4  1
  ORACLE  nchar  NCLOB  NULL  0  1
  ORACLE  ntext  NCLOB  NULL  0  1
  ORACLE  numeric  NUMBER  -1  3  1
  ORACLE  nvarchar  NCLOB  NULL  0  1
  ORACLE  nvarchar  NVARCHAR2  -1  4  1
  ORACLE  nvarchar(max)  NCLOB  NULL  0  1
  ORACLE  real  REAL  NULL  0  1
  ORACLE  smalldatetime  DATE  NULL  0  1
  ORACLE  smallint  NUMBER  5  3  1
  ORACLE  smallmoney  NUMBER  10  3  1
  ORACLE  sysname  NVARCHAR2  128  4  1
  ORACLE  text  CLOB  NULL  0  1
  ORACLE  timestamp  RAW  8  4  1
  ORACLE  tinyint  NUMBER  3  3  1
  ORACLE  uniqueidentifier  CHAR  38  4  1
  ORACLE  varbinary  BLOB  NULL  0  1
  ORACLE  varbinary  RAW  -1  4  1
  ORACLE  varbinary(max)  BLOB  NULL  0  1
  ORACLE  varchar  CLOB  NULL  0  1
  ORACLE  varchar  VARCHAR2  -1  4  1
  ORACLE  varchar(max)  CLOB  NULL  0  1
  ORACLE  xml  NCLOB  NULL  0  1