Datatypes translation between Oracle and SQL Server

Datatypes translation between Oracle and SQL Server part 1:character, binary strings

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 a article in the series so we talking about translate SQL query among different databases.

This article would focused on conversion of those datatypes:character, binary strings between Oracle and SQL Server. We'll 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 are not the only need to find corresponding data The type name is also need to the find out how string is stored in database. Is this string stored in character or byte? And you must is aware of the maximum length of datatype in source and target databases.

In SQL Server, char [(n)] was fixed-length, non-unicode character data with a length of n bytes. n must is a value from 1 through 8,000. You can easily the find corresponding datatype name "char" in Oracle, but the char in Oracle with a maximum length of $ bytes. So your 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 is also is used in fixed-length character data of length size in characters. When you use Char qualifier, for example char (ten 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 bytes to 4 bytes, depending on the database character set. When translate this datatype to SQL Server, target datatype can be char (ten) or char (+) 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 is summary tables show how Character and binary string datatypes translated from Oracle to SQL Server and Vice vers E.

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 strings 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

Datatypes translation between Peoplesoft and Oracle:

Note: LONGColumns cannot appear in these parts of SQL statements:

• GROUPBYclauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements

• The UNIQUE operator of a SELECT statement

• The column list of a CREATE CLUSTER statement

• The CLUSTER clause of a CREATE MATERIALIZED VIEW statement

• SQL built-in functions, expressions, or conditions

• SELECTLists of queries containing GROUP BY clauses

• SELECTLists of subqueries or queries combined by UNION the, INTERSECT , or MINUS set operators

• SELECTLists of ... CREATE TABLE AS SELECT statements

• ALTERTABLE ... MOVE Statements

• SELECTLists in subqueries in INSERT statements

The To_lob function is a very special function, except that this function can handle long data, and this function, like the long type, has a lot of restrictions. However, these are not very special places, the following simple look at To_lob this function.

The long type of Oracle is "notorious" because there are too many restrictions on the long type, which makes it very rare for Oracle to mention the limitations of the long type, usually by stating in which cases the long type can be used.

It is these restrictions that prevent the use of long, and Oracle also strongly recommends that you do not use the long type after the large object type--lob has been introduced.

Ironically, however, Oracle recommends that users stop using the long type, but in the data dictionary, long can be seen everywhere. Moreover, even the highest version of the 10r2,long type currently in use is still ubiquitous in the data dictionary. It is not known whether Oracle is considering compatibility issues or other reasons, but Oracle still does not remove the long type from the data dictionary. Do not know whether the 11g has changed.

Although Oracle does not do it itself, it is still recommended that users do not use long and use BLOBs, CLOB to replace long fields in existing systems. And the long type limit is really a headache, the tool that converts the long type to LOB type is the To_lob function.

The To_lob function, like the long type, has many limitations. Simply put, To_lob is typically used only in subqueries that follow the CREATE TABLE or Insert Table statement. Use in other places will error, such as UPDATE statement.

This is not the biggest problem, the biggest problem is that the To_lob function does not seem to really convert the long type to LOB data type. Personally, Oracle just does some processing of the long type so that it can be stored in a single LOB type.

Sql> CREATE TABLE T1 (ID number, TEXT CLOB);

The table is created.

Sql> CREATE TABLE T2 (ID number, TEXT VARCHAR2 (4000));

The table is created.

Sql> INSERT into T1 SELECT ROWNUM, TEXT from Dba_views;
INSERT into T1 SELECT ROWNUM, TEXT from Dba_views
* Error on line 1th:
Ora-00997:illegal Use of LONG datatype


Sql> INSERT into T1 SELECT ROWNUM, To_lob (TEXT) from Dba_views;

2268 rows have been created.

Sql> COMMIT;

Submit complete.

You can insert long data into the Clob field using To_lob, but if you want to insert a long data into VARCHAR2:

Sql> INSERT into T2 SELECT ROWNUM, TEXT from Dba_views;
INSERT into T2 SELECT ROWNUM, TEXT from Dba_views
* Error on line 1th:
Ora-00997:illegal Use of LONG datatype


Sql> INSERT into T2 SELECT ROWNUM, Dbms_lob. SUBSTR (To_lob (TEXT), 4000, 1) from Dba_views;
INSERT into T2 SELECT ROWNUM, Dbms_lob. SUBSTR (To_lob (TEXT), 4000, 1) from Dba_views
* Error on line 1th:
Ora-00932:inconsistent Datatypes:expected-got LONG

Direct insertion certainly not, but just already got the CLOB type, then will clob convert to VARCHAR2 not be OK? But the results were unexpected. Observing the error message, Oracle considers the returned data type to be long. It seems that To_lob does not convert data types. Here's a second test:

Sql> SELECT DUMP (To_lob (TEXT)) from Dba_views;
SELECT DUMP (To_lob (TEXT)) from Dba_views
* Error on line 1th:
Ora-00932:inconsistent Datatypes:expected-got LONG


Sql> SELECT DUMP (TEXT) from T1;
SELECT DUMP (TEXT) from T1
* Error on line 1th:
Ora-00932:inconsistent Datatypes:expected-got CLOB

It is clear from this comparison that the To_lob function does not return the CLOB type as imagined, but actually returns a long type.

Sql> INSERT into T2 SELECT ROWNUM, To_lob (TEXT) from Dba_views;

2268 rows have been created.

Direct use of To_lob seems to be possible, but a closer look at the results will reveal that the long type data is not actually inserted into the table:

sql> COL TEXT FORMAT A50
Sql> SET LONG 50
Sql> SELECT * from T2 WHERE ROWNUM < 3;

ID TEXT
---------- --------------------------------------------------
1
2

Sql> SELECT * from T1 WHERE ROWNUM < 3;

ID TEXT
---------- --------------------------------------------------
1 Select OWNER, table_name, Tablespace_name, CLUSTER
2 Select A.apply_name, A.queue_name, A.queue_owner,

Related Links

• Datatypes translation between Oracle and SQL Server part 2:number (8/4/2010)
• A List of SQL best Practices (10/3/2011)
• Http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm (Oracle Help Center, datatype)

Datatypes translation between Oracle and SQL Server