Datatypes translation between Oracle and SQL Server part 2: number
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).
| 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 |
| 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