OraFAQ Forum: Replication » Oracle data type mapping for Replication

Home » Server Options » Replication » Oracle data type mapping for Replication (SQL Server 2008 on windows2008 server, Oracle 11g R2 on linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle data type mapping for Replication [message #617996]

Sun, 06 July 2014 10:35

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

Report message to a moderator

Re: Oracle data type mapping for Replication [message #617999 is a reply to message #617996]

Sun, 06 July 2014 10:48

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Oracle data type mapping for Replication [message #618006 is a reply to message #617999]

Sun, 06 July 2014 11:56

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

Report message to a moderator

Re: Oracle data type mapping for Replication [message #618007 is a reply to message #618006]

Sun, 06 July 2014 12:13

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 14:30:49 CST 2025