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
A correlation name can be defined in the from clause of a query and in the first clause of an update or delete statement.
for example, the clause FROM X.MYTABLE Z establishes Z as a correlation name for X.MYTABLE.
With Z defined as correlation name of X.MYTABLE, only Z can be used to qualify reference to a column of that instance of X.MYTABLE in that SELECT statement.
If a correlation name is specified for a table, view, nickname or alias name, any qualified reference to a column of that table, view, nickname or alias must use the correlation name, rather than the table, view, nickname or alias name.
Example
SELECT *
FROM employee e
WHERE employee.project = ’abc’ /* incorrect */
The qualified reference to PROJECT should instead use the correlation name, ″E″, as shown below:
SELECT *
FROM employee e
WHERE e.project = ’abc’
below is also valid, there is no qualified reference to PROJECT.
SELECT *
FROM employee e
WHERE project = ’abc’
A fullselect is a form of a query that may be used as a component of various SQL statement. A fullselect used within a search condition of any statement is called a subquery. A fullselect used to retrieve a single value as an expression within a statement is called a scalar fullselect or scalar subquery. A fullselect used in the FROM clause of a query is called a nest table expression.
Subqueries in search conditions, scalar subqueries and nested table expression are referred to as subqueries through the remainder of this topic.
A subquery may includes subqueries of its own, and these may, in turn, include subqueries. Thus an SQL statement may contain a hierarchy of subqueries. Those elements of the hierarchy that contain subqueries are said to be at a higher level than the subqueries they contain.
Every element of the hierarchy contains one or more table designators. A subquery can reference not only the columns of the tables at its own level in the hierarchy, but also the columns of the tables identified previously in the hierarchy, back to the highest level of the hierarchy. A reference to a column of a table identified at a higher level is called a correlated reference.
For compatibility with existing standards for SQL, both qualified and unqualified column names are allowed as correlated reference. However, it is good practice to qualify all column references used in subqueries.
When a column name in a subquery is qualified, each level of hierarchy is searched, starting at the same query as the qualified column name appears and continuing to the higher levels of hierarchy until a table designator that matches the qualifier is found. Once found, it is verified that the table contains the given column. If the table is found at a higher level than the level containing column name, then it is a correlated reference to the level where the table designator was found.
DB2
SELECT versionnumber,
version_timestamp,
Substr(authid, 1, 20)
FROM sysibm.sysversions
MySQL
SELECT Version();
SQL SERVER
SELECT @@VERSION
Oracle
SELECT *
FROM v$version;
characters, the basic symbols of keywords and operators in SQL language are the single-byte characters that are part of all IBM character sets.
charaters of language are classified as letters, digits or special characters.
A letter is any of the 26 uppercase (A through Z) or 26 lowercase (a through z) letters, plus the three characters $, #, and @, which are included for compability with host database products.
A digit is any of the charaters 0 through 9.
A special character is any of characters listed below:
| Character | Description | Character | Description |
| space or blank | - | minus sign | |
| “ | quotation mark/double quote/double quotation mark | . | period |
| % | percent | / | slash |
| & | ampersand | : | colon |
| ‘ | apostrophe/single quote/singe quotation mark | ; | semicolon |
| ( | left parenthesis | < | less than |
| ) | right parenthesis | = | equals |
| * | asterisk | > | greater than |
| + | plus sign | ? | question mark |
| , | comma | _ | underline/underscore |
| | | vertical bar | ^ | caret |
| ! | exclamation mark | [ | left bracket |
| { | left brace | ] | right bracket |
| } | right brace |
All multi-byte characters are treated as letters, except for the double-byte blank, which is a special character.
You are currently browsing the archives for the reference category.
