sql与oracle互通数据,Oracle与SQL Server数据互用性–（1）

Most of us work with and specialize in only one RDBMS system, such as MSSQL, Oracle, or IBM DB2. However, increasingly, we find outselves dealing with a heterougeous database environment and are asked to solve data interoperability probelms.

Although the major RDBMS vendors try to follow the relational data model principals, their implementation of them can be quite different. In addition, although most of the major RDBMS vendors also comply with SQL-92 standard, they all have their own proprietary extension to that standard. For example, both Oracle's PL/SQL and Microsoft's T-SQL added tons of extra extentions to ANSI SQL.

In a series of articles, I want to address data interoperability issues between SQL Server and Oracle. I will talk about data types used in these 2 different RDBMS. In particular, I will talk about Oracle's timestamp and SQL Server's datetime, because their incompatibility can cause a lot of problems in DTS, linked server, and distributed queries.

Overview of data type differences between Oracle and SQL Server

For the most part, Oracle and SQL Server has compatible data types, although they may have different names , width, precision, and/or scale. For example, Oracle's CHAR data type corresponds to SQL Server's CHAR data type but they have different width. In Oracle, CHAR can hold maximum 2000 bytes. In SQL Server, it can hold maximum 8000 bytes. VARCHAR in SQL Server corresponds to VARCHAR2 in Oracle. Again, their width is different (Oracle VARCHAR2 4000, SQL Server VARCHAR 8000).

There are also differences in numeric data types. In Oracle, there is pretty much just one numeric data type, aptly named NUMBER. By defining its precision and scale, it matches to SQL Server's tinyint, smallint, int, bigint, and numeric data types. In Oracle, you can create a sequence, which can match SQL Server's Identity field.

For binary data types, Oracle has RAW, LONG RAW, and BLOB. They should be compatible with the varbinary and image data type in SQL Server. I say "should" because I didn't test this myself. Maybe I will do a binary data type comparison as my next project

. For more details on data types, please consult Microsoft SQL Server BOL and Oracle documentation.

For an Oracle table, to create a corresponding table in SQL Server, the best method is to use DTS Import wizard to generate the CREATE TABLE DDL statement. However, if the Oracle table has a TIMESTAMP column, you will have problems, which I will address in the next section.

Issues with Oracle TIMESTAMP and Microsoft SQL Server datetime conversion

In Oracle, the TIMESTAMP datatype enables you to resolve time to the billionth of a second. Although SQL Server has a type called timestamp, it is not the same as the timestamp data type defined in the SQL-92 standard. Its name is misleading for a lot of users. Typically, a timestamp data type in SQL Server is used as a mechanism for version-stamping table rows. Therefore, it does not match the timestamp in Oracle. The closest data type to Oracle's timestamp is datetime in SQL Server.

In SQL Server, the DATETIME data type has an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds. This inconsistency will cause problems if you try to do DTS data transformation from Oracle to SQL Server, or when you try to run a query via linked server that reference the timestamp column in Oracle.

Within DTS, if you use Microsoft ODBC driver for Oracle, this is the message you will get:

Within DTS, if you use Microsoft OLE DB Provider for Oracle, this is the message you will get

You can reproduce those error by following the steps below:

Create a table in Oracle by using this DDL:

create table t1 (c1 varchar2(20), c2 timestamp(3));

Note: The parameter of the timestamp column does not make a difference as far as this discussion is concerned. The transformation of timestamp data will fail no matter what kind of parameter you use. I tested this myself;

Insert some values into this Oracle test table, for example:

insert into t1 (c1, c2) values ('Hello World', systimestamp); commit;

In SQL Server Enterprise Manager, assuming you have Oracle Client installed and set up correctly, the process of which may warrant another article, you can use DTS designer and create a Data Transformation task from Oracle to SQL Server. When you try to preview data from t1, you will see the error I described above;

Now create a linked server. I may write a separate article for this. After this is done, you can issue a query like this:

select * from MyLinkedServer..MYSCHEMA.T1.

This is the error message you will get:

Server: Msg 7354, Level 16, State 1, Line 1

OLE DB provider 'MSDAORA' supplied invalid metadata for column 'C2'. The data type is not supported.

OLE DB error trace [Non-interface error: Column 'C2' (ordinal 1) of object '"MYSCHEMA"."T1"' reported an unsupported value for DBTYPE of 13].

If you run the linked server query using OpenQuery like this:

select * from openquery(MyLinkedServer, 'select * from MYSCHEMA.T1')

this is the message you will get:

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'MSDAORA' reported an error.

[OLE/DB provider returned message: Oracle error occurred, but error message could not be retrieved from Oracle.]

[OLE/DB provider returned message: Data type is not supported.]

OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80004005: ].

How to work around this problem

To work around this problem, you have to somehow cast Oracle's timestamp value to a type that SQL Server understands. One tradeoff is that you may lose some precision over this, maybe in the neighborhood of 3 milliseconds.

The PL/SQL function to use is TO_CHAR. If you are using Linked Server, use a query like this:

select * from openquery(MyLinkedServer, 'select TO_CHAR(systimestamp, ''YYYY-MM-DD HH24:MI:SSXFF3'') from dual')

. Please note that you have to use OpenQuery.

If you are using DTS, there are 2 ways to get it work. The first approach is to create a view in Oracle and use TO_CHAR to cast the timestamp value as a value that SQL Server understands. The other approach is to use a SQL statement as the data source and embed the TO_CHAR function within the SQL statement.

Conclusion

In this article, I described a few subtle differences between Oracle and SQL Server. Specifically, I listed some techniques dealing with Oracle timestamp values. Hopefully they can help you in solving your data interoperability issues.