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 1OLE 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 1OLE 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 值的方法。希望能够帮助你解决数据的互用性问题。

<script type="text/javascript"><!--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 = "";// --></script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"></script>

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