Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# Datatypes translation between Oracle and SQL Server part 2: number

- admin
- August 2, 2010
- sql, translation

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

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 (1send 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

### Newsletter Updates

Enter your email address below to subscribe to our newsletter

I accept the Privacy Policy

### Related Posts

#### Gudu SQLFlow direct data flow introduction

- May 26, 2022

#### Introduction to where-group-by clause in Gudu SQLFlow indirect data flow

- May 25, 2022

#### How to Get Data Lineage by Analyzing Oracle’s SQL Statements and Stored Procedures?

- May 23, 2022