作者 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,你将得到下面的提示:
通过下面的步骤,你可以再现这些错误消息:
- 使用DDL在Oracle中建一个表:
create table t1 (c1 varchar2(20), c2 timestamp(3));
注意:timestamp 字段的参数使用要正确,无论你使用何种参数,timestamp数据的变换都将失败。
- 在Oracle的表中插入一些值,例如:
insert into t1 (c1, c2) values ('Hello World', systimestamp); commit;
- 在 SQL Server 企业管理器中,假设你的Oracle客户端已经安装正确,在其他章节可能介绍与此相关内容。在Oracle到SQL Server直接你能够使用DTS designer 并且设计一个数据传输任务。当你想从t1中查询数据的时候,上面的错误提示将出现;
- 现在设计一个链接服务,我可以写一个独立的章节。你可以设计一个查询:
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].
- 如果你使用 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 值的方法。希望能够帮助你解决数据的互用性问题。
<script type="text/javascript"> </script> <script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"></script>