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

短命女 2022-08-04 10:47 125阅读 0赞

### 作者 [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 **数据**类型。 我说“应该”，是因为我还没有自己测试过这一点。或许在下一次我可以进行这个工作 ![:)][regular_smile.gif]。更多的**数据**类型的细节，请参考 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 驱动，将出现下面的消息提示：  
![ODBC Error][]  
在 DTS中，如果你使用 Microsoft OLE DB Provider for **Oracle**，你将得到下面的提示：  
![OLE DB Error][]  
通过下面的步骤，你可以再现这些错误消息：

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

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

[Haidong Ji]: http://www.sqlservercentral.com/Authors/Articles/Haidong_Ji/14430/
[GoodKid]: http://goodkid.2kmegs.com/
[regular_smile.gif]: http://goodkid.2kmegs.com/wp-content/plugins/fckeditor-for-wordpress-plugin/ckeditor/plugins/smiley/images/regular_smile.gif
[ODBC Error]: /images/20220731/338ae12fa7064a479fbe70fb5130b4e4.png
[OLE DB Error]: /images/20220731/e79b47e8e2c14af492fe4b9b770284ac.png
[VBScript]: http://goodkid.2kmegs.com/?p=249
[VBS]: http://goodkid.2kmegs.com/?p=92
[Oracle_2]: http://goodkid.2kmegs.com/?p=263
[Oracle_SQL Server_1]: http://goodkid.2kmegs.com/?p=258
[CTDP linux _ _4.2_ Linux]: http://goodkid.2kmegs.com/?p=33