Oracle与SQL Server数据互易性（1）

作者Haidong Ji翻译 GoodKid

我们当中的大部分人工作在一个单一的 RDBMS 系统中，如 MSSQL, Oracle, or IBM DB2。然而，我们日益感觉到，我们正处于不同的数据库环境当中并且需要解决数据的互用性问题。

尽管主要的 RDBMS 厂商试图去遵循关系数据库模型原理，并且用非常小的差异去实现它们。另外，几乎主要的 RDBMS 厂商都遵循 SQL-92 标准。他们仍然有他们自己的对该标准的扩展。例如，Oracle 的 PL/SQL 和 Microsoft 的 T-SQL ，对 ANSI SQL进行了更多的扩展。

通过一系列的文章，我将指明在 SQL Server和 Oracle 之间的数据互用性。我将讨论在两种不同的 RDBMS 中的数据类型。专门的，我将讨论 Oracle 的timestamp 和 SQL Server 的 datetime，在DTS，连接服务器和分布式查询中，它们的互用性可能导致许多问题。

Oracle和 SQL Server 数据类型不同点的概述

绝大部分，Oracle 和 SQL Server 有着兼容的数据类型，尽管它们有着不同的名字，以及精度或比例。例如：Oracle 的CHAR 对应于 SQL Server 的CHAR，但是它们的宽度不同。在 Oracle, CHAR 最大到2000 bytes。在 SQL Server，则可以达到 8000 bytes。SQL Server 的VARCHAR 对应 Oracle 的VARCHAR2。并且它们的宽度是不同的 (Oracle VARCHAR2 4000, SQL Server VARCHAR 8000) 。在数字类型方面也有不同。在 Oracle,几乎只有一个数字类型NUMBER，依靠定义它的精度和比例，它可以匹配 SQL Server 的tinyint, smallint, int, bigint,以及 numeric 数据类型。在 Oracle 中，你可以制作一个序列，它可以匹配 SQL Server 的唯一标识字段。

在二进制数据类型方面，Oracle具有 RAW, LONG RAW,和 BLOB等类型。它们应该能够兼容SQL Server中的 varbinary和 image 数据类型。 我说“应该”，是因为我还没有自己测试过这一点。或许在下一次我可以进行这个工作 。更多的数据类型的细节，请参考 Microsoft SQL Server BOL和 Oracle 的文档。

对于一个Oracle的表，在SQL Server中做一个对应的表，最好的办法是使用 DTS 导入向导生成一个 CREATE TABLE DDL 语句。如果Oracle的表有一个TIMESTAMP 字段，你将面临一些问题，下一节我将指明。

Oracle TIMESTAMP和 Microsoft SQL Server datetime 转换问题

在 Oracle 中，TIMESTAMP 类型使你解析时间的精度是10亿份之一秒。尽管SQL Server 有一个叫timestamp的类型，但是它和SQL-92中的timestamp的定义不同。它的名字使得许多的用户产生误解。典型的，SQL Server的 timestamp数据类型被用于 version-stamping 表的行。然而，它与Oracle中的 timestamp不能匹配。与Oracle中的timestamp最接近的数据类型是SQL Server中的datetime。

在SQL Server中，DATETIME 数据类型具有300分之一秒的精度 (等于3.33 毫秒或 0.00333 秒)。以 .003,或 .007 秒递增。这个矛盾将导致在试图进行DTS数据传输时的问题，或者当你通过连接服务器在Oracle中查询相关的timestamp字段时也会出现问题。

在DTS 中，如果你使用 Microsoft ODBC 驱动，将出现下面的消息提示：

在DTS中，如果你使用 Microsoft OLE DB Provider for Oracle，你将得到下面的提示：

通过下面的步骤，你可以再现这些错误消息：

1. 使用DDL在Oracle中建一个表：

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

   注意：timestamp 字段的参数使用要正确，无论你使用何种参数，timestamp数据的变换都将失败。

2. 在Oracle的表中插入一些值，例如：

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

3. 在 SQL Server 企业管理器中，假设你的Oracle客户端已经安装正确，在其他章节可能介绍与此相关内容。在Oracle到SQL Server直接你能够使用DTS designer 并且设计一个数据传输任务。当你想从t1中查询数据的时候，上面的错误提示将出现；
4. 现在设计一个链接服务，我可以写一个独立的章节。你可以设计一个查询：

   select * from MyLinkedServer..MYSCHEMA.T1.

   将获得下面的错误：

   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].

5. 如果你使用 OpenQuery：

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

   将获得下面的错误：

   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:   ].

围绕这个问题如何解决呢

为了解决这个问题，你必须转换Oracle的timestamp值以便SQL Server能够接受它。一个方法是，你可以丢掉一些精度，使它接近3毫秒。

对应的 PL/SQL 函数是 TO_CHAR。如果你使用连接服务器，如下的方法进行查询：

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

. 请注意你必须使用OpenQuery。

如果你使用 DTS，有两个方法。第一个方法是在Oracle中设计一个视图view，并且使用 TO_CHAR 去转换 timestamp 的值使得 SQL Server 可以识别。另一个方法是使用SQL语句作为数据源并且在SQL语句中嵌入 TO_CHAR 函数。

结论

在本文中，我描述了在 Oracle和 SQL Server 之间的一些不同点。特别的，我列举了一些处理 Oracle timestamp 值的方法。希望能够帮助你解决数据的互用性问题。

<!-- google_ad_client = "pub-2416224910262877"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "image"; google_ad_channel = ""; // -->

• 使用VBScript自动化任务
• 通过对一个病毒源码的分析，了解VBS脚本语言的应用
• Oracle驱动的比较–（2）
• Oracle与SQL Server数据互易性（1）
• CTDP linux 程序员手册 (4.2) Linux 测试条件