Home » Server Options » Replication » Oracle data type mapping for Replication (SQL Server 2008 on windows2008 server, Oracle 11g R2 on linux)
Oracle data type mapping for Replication [message #617996] Sun, 06 July 2014 10:35 Go to next message
chetankumar
Messages: 20
Registered: June 2014
Location: Dark Saturn
Junior Member
Hello

We are trying to replicate database from SQL Server 2008 to Oracle 11g R2 database (on linux) using Golden Gate Software 11g

Before we do 'Initial load' we want to create the corresponding table structure and related objects in the Oracle database

I searched for the data type comparison between the 2 database systems and was confused on few points
Ex.
float in SQL server is FLOAT(53) or FLOAT(63) or FLOAT(126) or NUMBER in Oracle?
nchar(n) in SQL server is nchar(n) or char(n*2) in Oracle?
ntext in SQL server is Long or nclob in Oracle?

Across various links I referred,I can summarised my understanding as follows?


bigint				number(19)
int				number(10)
tinyint				number(3)	
smallint			number(5) 
money				number(19,4)
float			        float
real				float(63) 
numeric				number(p[,s]) 
bit				number(3) or number(1)
date				date
datetime			date    (unless we need precision upto second else timestamp)
image				blob
text				clob
ntext				clob
uniqueidentifier	        char(36)
char(n)				varchar2(n)
varchar(n)			varchar2(n)
varchar (max)		        clob
nchar(n)			varchar2(2*n)
nvarchar			varchar2(2*n)    
sysname				varchar2(30)



Could you please let me know if my understanding is correct on above datatypes?
Any hints, suggestions??

Thanks and Regards
Chetan
Re: Oracle data type mapping for Replication [message #617999 is a reply to message #617996] Sun, 06 July 2014 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL Reference, ANSI, DB2, and SQL/DS Data Types section.

Re: Oracle data type mapping for Replication [message #618006 is a reply to message #617999] Sun, 06 July 2014 11:56 Go to previous messageGo to next message
chetankumar
Messages: 20
Registered: June 2014
Location: Dark Saturn
Junior Member
Hello Michel

Thanks for the quick reply!!

I referred the link mentioned by you.
In it shall I consider ANSI as SQL Server datatype which is pertaining to my question?

In that case there is some confusion

(http://docs.oracle.com/cd/E10405_01/doc/appdev.120/e10379/ss_oracle_compared.htm) - Table 2-2 Data Types in Oracle and Microsoft SQL Server
V/s
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#SQLRF00213
- This one you have sent

For Example :

Link No 1 -
SQL server Oracle
NCHAR(n) CHAR(n*2)

Link No 2 -
SQL server Oracle
NCHAR(n) NCHAR(n)

Could you please suggest on this?

Also the link you have referred does not mention comparative datatypes for image etc.

Thanks and Regards
Chetan
Re: Oracle data type mapping for Replication [message #618007 is a reply to message #618006] Sun, 06 July 2014 12:13 Go to previous message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think you should follow the documentation for your Oracle version (the one I posted) although I am not sure it is correct.
I don't know SQL Server, so don't know what NCHAR means for it.
If you have a support contract with Oracle, I advise you to open a SR on this question.

Edit: in the end, have you some NCHAR columns? if no you could skip this question.

[Updated on: Sun, 06 July 2014 12:14]

Report message to a moderator

Previous Topic: create DDL scripts from SQL server to Oracle
Next Topic: Is it possible to use pump process during initial load in Golden Gate?
Goto Forum:
  


Current Time: Mon Sep 28 02:10:49 CDT 2020