{"id":494,"date":"2010-07-28T21:17:35","date_gmt":"2010-07-29T02:17:35","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?p=494"},"modified":"2011-09-26T21:48:27","modified_gmt":"2011-09-27T02:48:27","slug":"datetypes-conversion-between-oracle-and-sql-server","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/","title":{"rendered":"Datatypes translation between Oracle and SQL Server part 1: character, binary strings"},"content":{"rendered":"

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

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

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

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

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

\nDetailed information about Oracle datatypes<\/a> and SQL Server datatypes<\/a>: including datatype name, description and what’s the corresponding datatype in other databases.\n<\/p>\n

\nBelow are summary tables show how Character and binary string datatypes translated from Oracle to SQL Server and vice verse.\n<\/p>\n

\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Oracle(source)<\/td>\nSQL Server(target)<\/td>\n<\/tr>\n
CHAR [(size [BYTE | CHAR])]<\/td>\nchar[(size)]<\/td>\n<\/tr>\n
VARCHAR2(size [BYTE | CHAR])<\/td>\nvarchar(size)<\/td>\n<\/tr>\n
NCHAR[(size)]<\/td>\nnchar[(size)]<\/td>\n<\/tr>\n
NVARCHAR2(size)<\/td>\nnvarchar(size)<\/td>\n<\/tr>\n
long<\/td>\nvarchar(max)<\/td>\n<\/tr>\n
long raw<\/td>\nvarbinary(max)<\/td>\n<\/tr>\n
raw(size)<\/td>\nvarbinary(size)<\/td>\n<\/tr>\n
blob<\/td>\nvarbinary(max)<\/td>\n<\/tr>\n
clob<\/td>\nvarchar(max)<\/td>\n<\/tr>\n
nclob<\/td>\nntext<\/td>\n<\/tr>\n
bfile<\/td>\nN\/A<\/td>\n<\/tr>\n<\/table>\n<\/div>\n

\nHow Character and binary string datatypes translated from SQL Server to Oracle.\n<\/p>\n

\n\n\n\n\n\n\n\n\n\n\n\n
SQL Server(source)<\/td>\nOracle(target)<\/td>\n<\/tr>\n
char [ ( n ) ] <\/td>\nchar[(n)], 1<=n<=2000; clob, n>2000<\/td>\n<\/tr>\n
varchar [ ( n | max ) ] <\/td>\nvarchar2(n), 1<=n<=4000; clob (n>4000)<\/td>\n<\/tr>\n
text<\/td>\nclob<\/td>\n<\/tr>\n
nchar [ ( n ) ] <\/td>\nnchar[(n)], 1<=n<=2000; nclob(n>2000)<\/td>\n<\/tr>\n
nvarchar [ ( n | max ) ]<\/td>\nnvarchar2[(n)], 1<=n<=4000; nclob( n>4000 )<\/td>\n<\/tr>\n
ntext<\/td>\nnclob<\/td>\n<\/tr>\n
binary [ ( n ) ]<\/td>\nraw(1)(n was omitted); raw(n), 1<=n<=2000; blob(n>2000)<\/td>\n<\/tr>\n
varbinary [ ( n | max) ] <\/td>\nraw(1)(n was omitted); raw(n), 1<=n<=2000; blob(n>2000)<\/td>\n<\/tr>\n
image<\/td>\nblob<\/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":"

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\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[16,25,17],"tags":[23],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nDatatypes translation between Oracle and SQL Server part 1: character, binary strings<\/title>\n<meta name=\"description\" content=\"Datatypes translation between Oracle and SQL Server part 1: character, binary strings\" \/>\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\/datetypes-conversion-between-oracle-and-sql-server\/\" \/>\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 1: character, binary strings\" \/>\n<meta property=\"og:description\" content=\"Datatypes translation between Oracle and SQL Server part 1: character, binary strings\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:published_time\" content=\"2010-07-29T02:17:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2011-09-27T02:48:27+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=\"2 minutes\" \/>\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\/datetypes-conversion-between-oracle-and-sql-server\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/\",\"name\":\"Datatypes translation between Oracle and SQL Server part 1: character, binary strings\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2010-07-29T02:17:35+00:00\",\"dateModified\":\"2011-09-27T02:48:27+00:00\",\"description\":\"Datatypes translation between Oracle and SQL Server part 1: character, binary strings\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/#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 1: character, binary strings\"}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/fd33d9e572b65788a80dc4bac2b4e5a7\"},\"headline\":\"Datatypes translation between Oracle and SQL Server part 1: character, binary strings\",\"datePublished\":\"2010-07-29T02:17:35+00:00\",\"dateModified\":\"2011-09-27T02:48:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/\"},\"wordCount\":406,\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"keywords\":[\"sql translate\"],\"articleSection\":[\"reference\",\"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 1: character, binary strings","description":"Datatypes translation between Oracle and SQL Server part 1: character, binary strings","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\/datetypes-conversion-between-oracle-and-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Datatypes translation between Oracle and SQL Server part 1: character, binary strings","og_description":"Datatypes translation between Oracle and SQL Server part 1: character, binary strings","og_url":"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/","og_site_name":"SQL and Data Blog","article_published_time":"2010-07-29T02:17:35+00:00","article_modified_time":"2011-09-27T02:48:27+00:00","author":"admin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"admin","Est. reading time":"2 minutes"},"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\/datetypes-conversion-between-oracle-and-sql-server\/","url":"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/","name":"Datatypes translation between Oracle and SQL Server part 1: character, binary strings","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2010-07-29T02:17:35+00:00","dateModified":"2011-09-27T02:48:27+00:00","description":"Datatypes translation between Oracle and SQL Server part 1: character, binary strings","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/#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 1: character, binary strings"}]},{"@type":"Article","@id":"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/#article","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/"},"author":{"name":"admin","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/fd33d9e572b65788a80dc4bac2b4e5a7"},"headline":"Datatypes translation between Oracle and SQL Server part 1: character, binary strings","datePublished":"2010-07-29T02:17:35+00:00","dateModified":"2011-09-27T02:48:27+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dpriver.com\/blog\/datetypes-conversion-between-oracle-and-sql-server\/"},"wordCount":406,"publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"keywords":["sql translate"],"articleSection":["reference","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\/494"}],"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=494"}],"version-history":[{"count":34,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/494\/revisions"}],"predecessor-version":[{"id":1015,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/494\/revisions\/1015"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=494"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/categories?post=494"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/tags?post=494"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}