Archive for the ‘translation’ Category

Datatypes translation between Oracle and SQL Server part 2: number

Monday, August 2nd, 2010

This is an article in the series that we talking about translate SQL query among different databases.

This article focus on the translation of number datatype between oracle and SQL Server database.

The main difference of number datatype between oracle and SQL Server is float. ANSI SQL requires float precision to be specified in terms of binary bits. But the number of binary bits specified in float definition of Oracle and SQL Server doesn’t have the same meanings.

In SQL Server, syntax of float datatype is float[(n)], Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53. SQL Server treats n as one of two possible values. If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53. The SQL Server float[(n)] data type complies with the ISO standard for all values of n from 1 through 53. The synonym for double precision is float(53).

Oracle uses its NUMBER datatype internally to represent float. precision of Oracle FLOAT range from 1 to 126 in binary bits, The maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision which is used by NUMBER datatype.

From this article “Internal representation of the NUMBER datatype”, you may agree with me that the number of binary bits specified in float definition of Oracle and SQL Server doesn’t have the same meanings. I don’t know how to map n in SQL server (1<=n<=53) to size in Oracle(1<=size<=126). If anybody know this, please kindly send me an email. But this article tells me how to map float from Oracle to SQL Server, 

float -> float
float(1-53) -> float(1-53)
float(54-126) -> float

and this article tells me how to map float from SQL Server to Oracle.

float -> float(49)
real, float(24) -> float(23)

 

Oracle allows numbers to be defined with a scale greater than the precision, such as NUMBER(4,5), but SQL Server requires the precision to be equal to or greater than the scale. To ensure there is no data truncation, if the scale is greater than the precision at the Oracle Publisher, the precision is set equal to the scale when the data type is mapped: NUMBER(4,5) would be mapped as NUMERIC(5,5).

Number datatype convert from Oracle to SQL Server
Oracle(source) SQL Server(target)
number float
number([1-38]) numeric([1-38])
number([0-38],[1-38]) numeric([0-38],[1-38])
float float
float([1-53]) float([1-53])
float([54-126]) float
binary_float float
binary_double float(53)
int numeric(38)
real float
Number datatype convert from SQL Server to Oracle
SQL Server(source) Oracle(target)
bigint NUMBER(19)
int NUMBER(10)
smallint NUMBER(5)
tinyint NUMBER(3)
bit NUMBER(3)
numeric NUMBER(p[,s])
money number(19,4)
smallmoney NUMBER(10,4)
float FLOAT(49)
real FLOAT(23)

  Reference: 1. Oracle datatypes 2. SQL Server datatypes

Posted in sql, translation | No Comments »

Datatypes translation between Oracle and SQL Server part 1: character, binary strings

Wednesday, July 28th, 2010

Datatypes translation is one of the most important things you need to consider when migrate your application from one database to the other. This is one of the series of articles that we talking about translate SQL query among different databases.

This article will focused on conversion of those datatypes: character, binary strings between Oracle and SQL Server. We will talk about conversion of other datatypes such as nunber, float, date and etc in other articles later.

When you convert character datatypes from Oracle to SQL Server or vice verse, you not only need to find corresponding datatype name but also need to find out how string was stored in database. Is this string stored in character or byte? and you must be aware of the maximum length of datatype in source and target databases.

In SQL Server, char [ ( n ) ] is fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. You can easily find corresponding datatype name “char” in Oracle, but char in oracle with a maximum length of 2000 bytes. So you can’t migrate char(2048) in your SQL Server script to Oracle without any changes, you should use clob instead if n > 2000.

In Oracle database, char[(size)] can be also be used in fixed-length character data of length size in characters. When you use char qualifier, for example char(10 char), then you supply the column length in characters. A character is technically a code point of the database character set. Its size can range from 1 byte to 4 bytes, depending on the database character set. When translate this datatype to SQL Server, target datatype can be char(10) or char(40) depends on the database character set in source database(Oracle).

Detailed information about Oracle datatypes and SQL Server datatypes: including datatype name, description and what’s the corresponding datatype in other databases.

Below are summary tables show how Character and binary string datatypes translated from Oracle to SQL Server and vice verse.

Oracle(source) SQL Server(target)
CHAR [(size [BYTE | CHAR])] char[(size)]
VARCHAR2(size [BYTE | CHAR]) varchar(size)
NCHAR[(size)] nchar[(size)]
NVARCHAR2(size) nvarchar(size)
long varchar(max)
long raw varbinary(max)
raw(size) varbinary(size)
blob varbinary(max)
clob varchar(max)
nclob ntext
bfile N/A

How Character and binary string datatypes translated from SQL Server to Oracle.

SQL Server(source) Oracle(target)
char [ ( n ) ] char[(n)], 1<=n<=2000; clob, n>2000
varchar [ ( n | max ) ] varchar2(n), 1<=n<=4000; clob (n>4000)
text clob
nchar [ ( n ) ] nchar[(n)], 1<=n<=2000; nclob(n>2000)
nvarchar [ ( n | max ) ] nvarchar2[(n)], 1<=n<=4000; nclob( n>4000 )
ntext nclob
binary [ ( n ) ] raw(1)(n was omitted); raw(n), 1<=n<=2000; blob(n>2000)
varbinary [ ( n | max) ] raw(1)(n was omitted); raw(n), 1<=n<=2000; blob(n>2000)
image blob

  Reference: 1. Oracle datatypes 2. SQL Server datatypes

Tags:
Posted in reference, sql, translation | No Comments »

  • You are currently browsing the archives for the translation category.

Gudu Software 2002-2011  Privacy Policy  Terms of Use  About Us