{"id":546,"date":"2010-08-02T23:27:41","date_gmt":"2010-08-03T04:27:41","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?p=546"},"modified":"2011-09-26T21:47:37","modified_gmt":"2011-09-27T02:47:37","slug":"datatypes-translation-between-oracle-and-sql-server-part-2-number","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/","title":{"rendered":"Datatypes translation between Oracle and SQL Server part 2: number"},"content":{"rendered":"

This is an article in the series that we talking about translate SQL query among different databases<\/a>.<\/p>\n

\nThis article focus on the translation of number datatype between oracle and SQL Server database.\n<\/p>\n

\nThe 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.\n<\/p>\n

\nIn 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).\n<\/p>\n

\nOracle 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.\n<\/p>\n

\nFrom this article “Internal representation of the NUMBER datatype”<\/a>, 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<\/a>. But this article<\/a> tells me how to map float from Oracle to SQL Server, <\/p>\n

\r\nfloat -> float\r\nfloat(1-53) -> float(1-53)\r\nfloat(54-126) -> float\r\n<\/pre>\n
and this article<\/a> tells me how to map float from SQL Server to Oracle.<\/p>\n
\r\nfloat -> float(49)\r\nreal, float(24) -> float(23)\r\n<\/pre>\n<\/p>\n
\n \n<\/p>\n
\nOracle 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).\n<\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n
Number datatype convert from Oracle to SQL Server<\/caption>\n
Oracle(source)<\/td>\nSQL Server(target)<\/td>\n<\/tr>\n
number<\/td>\nfloat<\/td>\n<\/tr>\n
number([1-38])<\/td>\nnumeric([1-38])<\/td>\n<\/tr>\n
number([0-38],[1-38])<\/td>\nnumeric([0-38],[1-38])<\/td>\n<\/tr>\n
float<\/td>\nfloat<\/td>\n<\/tr>\n
float([1-53])<\/td>\nfloat([1-53])<\/td>\n<\/tr>\n
float([54-126])<\/td>\nfloat<\/td>\n<\/tr>\n
binary_float<\/td>\nfloat<\/td>\n<\/tr>\n
binary_double<\/td>\nfloat(53)<\/td>\n<\/tr>\n
int<\/td>\nnumeric(38)<\/td>\n<\/tr>\n
real<\/td>\nfloat<\/td>\n<\/tr>\n<\/table>\n<\/div>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n
Number datatype convert from SQL Server to Oracle<\/caption>\n
SQL Server(source)<\/td>\nOracle(target)<\/td>\n<\/tr>\n
bigint<\/td>\nNUMBER(19)<\/td>\n<\/tr>\n
int<\/td>\nNUMBER(10)<\/td>\n<\/tr>\n
smallint<\/td>\nNUMBER(5)<\/td>\n<\/tr>\n
tinyint<\/td>\nNUMBER(3)<\/td>\n<\/tr>\n
bit<\/td>\nNUMBER(3)<\/td>\n<\/tr>\n
numeric<\/td>\nNUMBER(p[,s])<\/td>\n<\/tr>\n
money<\/td>\nnumber(19,4)<\/td>\n<\/tr>\n
smallmoney<\/td>\nNUMBER(10,4)<\/td>\n<\/tr>\n
float<\/td>\nFLOAT(49)<\/td>\n<\/tr>\n
real<\/td>\nFLOAT(23)<\/td>\n<\/tr>\n<\/table>\n<\/div>\n
\n 
\nReference:
\n1. Oracle datatypes<\/a>
\n2. SQL Server datatypes<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[25,17],"tags":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nDatatypes translation between Oracle and SQL Server part 2: number<\/title>\n<meta name=\"description\" content=\"Datatypes translation between Oracle and SQL Server part 2: number\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Datatypes translation between Oracle and SQL Server part 2: number\" \/>\n<meta property=\"og:description\" content=\"Datatypes translation between Oracle and SQL Server part 2: number\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:published_time\" content=\"2010-08-03T04:27:41+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2011-09-27T02:47:37+00:00\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/\",\"name\":\"Datatypes translation between Oracle and SQL Server part 2: number\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2010-08-03T04:27:41+00:00\",\"dateModified\":\"2011-09-27T02:47:37+00:00\",\"description\":\"Datatypes translation between Oracle and SQL Server part 2: number\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Datatypes translation between Oracle and SQL Server part 2: number\"}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/fd33d9e572b65788a80dc4bac2b4e5a7\"},\"headline\":\"Datatypes translation between Oracle and SQL Server part 2: number\",\"datePublished\":\"2010-08-03T04:27:41+00:00\",\"dateModified\":\"2011-09-27T02:47:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/\"},\"wordCount\":154,\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"articleSection\":[\"sql\",\"translation\"],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/fd33d9e572b65788a80dc4bac2b4e5a7\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/61d4e409068502b7e4960eb89efb79f8?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/61d4e409068502b7e4960eb89efb79f8?s=96&d=mm&r=g\",\"caption\":\"admin\"},\"url\":\"https:\/\/www.dpriver.com\/blog\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Datatypes translation between Oracle and SQL Server part 2: number","description":"Datatypes translation between Oracle and SQL Server part 2: number","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/","og_locale":"en_US","og_type":"article","og_title":"Datatypes translation between Oracle and SQL Server part 2: number","og_description":"Datatypes translation between Oracle and SQL Server part 2: number","og_url":"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/","og_site_name":"SQL and Data Blog","article_published_time":"2010-08-03T04:27:41+00:00","article_modified_time":"2011-09-27T02:47:37+00:00","author":"admin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"admin","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/","url":"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/","name":"Datatypes translation between Oracle and SQL Server part 2: number","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2010-08-03T04:27:41+00:00","dateModified":"2011-09-27T02:47:37+00:00","description":"Datatypes translation between Oracle and SQL Server part 2: number","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Datatypes translation between Oracle and SQL Server part 2: number"}]},{"@type":"Article","@id":"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/#article","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/"},"author":{"name":"admin","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/fd33d9e572b65788a80dc4bac2b4e5a7"},"headline":"Datatypes translation between Oracle and SQL Server part 2: number","datePublished":"2010-08-03T04:27:41+00:00","dateModified":"2011-09-27T02:47:37+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dpriver.com\/blog\/2010\/08\/datatypes-translation-between-oracle-and-sql-server-part-2-number\/"},"wordCount":154,"publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"articleSection":["sql","translation"],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/fd33d9e572b65788a80dc4bac2b4e5a7","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/61d4e409068502b7e4960eb89efb79f8?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/61d4e409068502b7e4960eb89efb79f8?s=96&d=mm&r=g","caption":"admin"},"url":"https:\/\/www.dpriver.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/546"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=546"}],"version-history":[{"count":24,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/546\/revisions"}],"predecessor-version":[{"id":1014,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/546\/revisions\/1014"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=546"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/categories?post=546"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/tags?post=546"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}