如何连接SQL Server与Oracle数据库

如何连接SQLServer与Oracle数据库

原文出处: 2001/11 SQL Server Magazine

原文标题: SQL Server and Oracle: Making the Connection

原作者: John Paul Cook

译者: 何致億

 

假定您现在拥有一个以Microsoft .NET为架构的网络订购系统，但是品管维护系统却仍然使用一套旧式的Oracle数据库应用程序。当您的顾客在产品保固期间下了产品更换之类的订单，则该笔订单将不收取任何费用。此时您需要从Oracle数据库得到实时的查询结果。借着建立连结服务器的方式(linked server)，您将可以从SQL Server实时查询出位于Oracle数据库的顾客资料，找出谁是您既有的客户。

当您的资料分散在不同的SQL Server数据库时，藉由连结服务器可让您执行跨服务器之分布式查询。当所有的数据库服务器都是SQL Server，则连结服务器的设定十分容易，而且在SQL Server线上手册中就涵盖了您所需要了解的所有事项。然而，当部分资料是放在Oracle数据库服务器的时候，这就可能带给您许多挑战。举例来说，光是设定连结服务器就不是一件容易的事。您必须了解到：即是您要在SQL Server的Enterprise Manager设定一个Oracle连结服务器，这台SQL Server对Oracle来说就是一个客户端。所以您必须在SQL Server所在的服务器成功地安装并组态Oracle之客户端软件。因为Oracle提供的产品只支持Oracle 8以后的数据库，所以我假设您正在使用的都是Oracle 8以后的数据库。在Oracle Net8函式库则提供了SQL Server所需要的客户端软件。

 

译者注1：设定连接服务器时是利用Microsoft所提供的OLE DB Provider for Oracle，使用的Oracle网络函式库为SQL*Net 2.3.3.0.4或是以后的版本，不过这是Oracle 7.3数据库所提供的。换句话说，要设定Oracle数据库为SQL Server的连接服务器时，Oracle数据库只要是7.3.3.4以后的版本，并搭配适当的SQL*Net或是Net 8 函式库即可。

(SQL*Net为Net8之前身)

资料来源：1. Oracle 7 Server Getting Started release 7.3.4

2. SQL Server Books Online: OLE DB Provider for Oracle

 

在Oracle数据库中，一个schema即代表着SQL Server专家们所熟知的单一数据库(译者注2)。连接至Oracle数据库时，您必须提供schema名称、密码以及主机联机字符串(host string)。每一个特定的Oracle帐户都拥有一个Oracle schema，而且只能有一个schema。所以schema名称其实就等于该schema拥有者的帐户名称。您可以查询Oracle的资料字典(Data dictionary)以得到更多有关schema的内容。

 

至于Oracle联机字符串又可称为服务名称(service name)或是系统识别资料(Systen Identifier，SID)。我们所谓的SQL Server数据库个体(instance)在Oracle则称为数据库(database)。所以安装Oracle Server时，安装程序Oracle Universal Installer(为一个图形接口之安装程序，与SQL Server的Setup程序类似)将会询问您SID名称为何，以作为Oracle数据库之名称。

 

译者注2：

原文为：In Oracle, aschemais the name for the entity SQL Server professionals know as a database.。

这个部分原作者所提到schema的解释有点问题。Oracle schema可视为同一个使用者所拥有的所有数据库对象(schema objects)之集合。举例来说，使用者scott所建立的EMP table其完整名称为SCOTT.EMP，而SCOTT就是EMP的schema名称。所以schema name其实就是一个Oracle数据库之使用者帐号。但是绝对不能拿来跟数据库相提并论！因为SQL Server的数据库架构包含了data files与log files，但是Oracle的schema objects只存在于tablespace中。为了避免部分读者产生混淆，特此说明。

 

如果您在Windows操作系统上安装Oracle Server时设定SID为Ora817(与本篇文章的范例相同)，则在系统内将会有一个名称为OracleServiceORA817的服务。

这个服务就类似于SQL Server 2000的MSSQLSERVER服务。如果您更进一部了解这两项产品在架构上有何差异的话，可参阅Microsoft提供的文件《Migrating Oracle Databases to Microsoft SQL Server 7.0》(http://msdn.microsoft.com/library/techart/oracle2sql.htm).

 

在SQL Server所在服务器设定Oracle之客户端组态

SQL Server是透过应用程序层级的TDS通讯协议(Tabular Data Stream)达成与其客户端互相交换资料之目的。而TDS还需搭配底层网络通讯协议，例如TCP/IP或是IPX/SPX。至于Oracle Server与客户端则是利用Net8在TNS(Transparent Network Substrate)、Oracle通讯协议转接接口、以及支持的网络函式库等三项组件之间互相交换资料。Net8在TNS与网络函式库之间利用一个通讯协议转接接口来完成直译的工作。取代掉SQL*Net的Net8会使用服务名称(service name)去搜寻服务器之位置。

Net8网络函式库有支持的通讯协议分别为：TCP/IP，SPX，Named Pipes，Logical Unit Type 6.2 (LU6.2)以及Bequeath。您可利用TCP/IP与SPX从远地客户端连至Oracle数据库。若是IBM的APPC(Advanced Program-to-Program Communications)系统架构，则可使用兼容的LU6.2。如果从本地端登入Oracle数据库，Net8联机的方式则是使用Bequeath 网络通讯协议。

为了让Net8客户端可以成功地与Oracle Server建立联机，客户端必须拥有可联机至Oracle Server的服务名称。Oracle客户端可以利用本地的tnsnames.ora去解析服务名称(类似Windows操作系统下的HOSTS 档案)。当然DNS或是Oracle的名称服务器(Oracle Names Server)也可以。Oracle 9i还提供了LDAP(Lightweight Directory Access Protocol)作为另外一种名称解析的方式。然而，目前Microsoft官方并没有正式支持Oracle 9i作为连结服务器。

 

以Java语言撰写的Oracle Universal Installer安装程序将可协助您在Windows与UNIX 系统安装Oracle软件。在Windows环境下，只要将安装程序光盘放入光驱就会自动激活Oracle Universal Installer。

 

我会建议您选择系统预设的选项进行安装，因为这样做除了会安装Net8之外，还会安装Net8辅助精灵(Net8 Assistant)、Net8组态设定精灵(Net8 Configuration Assistant)以及SQL*Plus。并且将以上各项目加到程序集中。SQL*Plus是一个客户端工具，类似于SQL Server的osql 工具程序。

 

问题排除

利用SQL Server检核Oracle数据库的联机会导致令人混淆的结果。除非您已执行连结服务器之查询指令，否则并无法确认连结服务器的设定是否正确。

记住一点，您可以间接地查询连结服务器的资料。在Enterprise Manager中，如果点选已设定之连结服务器，并开启Tables或是Views资料夹，您就是间接地在查询Oracle数据字典的数据。在我利用这种方法执行查询之后，收到了如下图1之错误讯息：

图1：查询Oracle资料字典之后得到的错误讯息。

 

但是当我从命令提示符号执行Oracle的TNSPING 指令时(类似TCP/IP PING指令，但是只能测试客户端与Oracle Server之联机是否正常)，得到的讯息却显示Oracle客户端与相关的网络组件已经成功安装至系统内，如下图2所示：

图2：执行TNSPING得到之讯息。

 

除此之外，我也成功地利用Net8辅助精灵(图3所示)以及Net8 组态设定精灵完成所有的设定，并与Oracle数据库建立好实际联机。我所使用的是SCOTT 的范例资料，有点类似SQL Server的Pubs数据库。无论使用以上任一种网络管理工具都足以检核数据库联机是否正常！所以，问题应该是发生在其它地方。

 

图3：Net8辅助精灵之设定窗口。

 

 

 

 

当您建立的新联结服务器欲连往非Microsof数据库时，请检查PATH这个环境变量。注意，在PATH变量中Oracle的路径出现在SQL Server的路径前面。这样是会有问题的！如下图4所示：

图4：不正确的PATH设定。

 

为了避免收到这样的错误讯息，PATH之设定需要做修改！请将SQL Server的路径设定放在任何数据库的设定前面，如下图5所示：

 

图5：修改后的PATH设定。

 

在我修正PATH的设定之后，就可以正确查询连结服务器的资料了。

 

组态SQL Server

在SQL Server 2000建立Oracle连结服务器需要额外的组态工作：必须在客户端计算机内登录一个新机码(以本例而言，您的SQL Server将是Oracle Server的客户端)。您必须为Windows操作系统与Oracle Server的版本选择一个适当的登录机码。在C:\program files\common files\system\ole db目录下可找到适当的登录档(registry files)，文件名称以mtx作为开头。双击适当的登录档之后加入必要的机码值。如果想获取更多这方面的信息，请查阅SQL Server线上手册，搜寻：Accessing and Changing Relational Data, Distributed Queries, OLE DB Providers Tested with SQL Server, OLE DB Provider for Oracle, 并查阅有关Registry Entries之相关资料。

您可藉由两道系统预储程序来建立连结服务器。一开始先执行sp_addlinkedserver指定您所选择的OLE DB provider。下面范例将设定为Microsoft Data Access for Oracle：

sp_addlinkedserver 'OraDB', 'Oracle', 'MSDAORA', 'Ora817'

 

或是您可以设定为Oracle's OLE DB provider：

sp_addlinkedserver 'OraDB', 'Oracle', 'OraOLEDB.Oracle', 'Ora817'

 

在上述方法中可任选其一。虽然Microsoft官方仅支持且建议使用Microsoft OLE DB Provider for Oracle，但是我接下来所要示范的INSERT指令中，使用此provider将会受到某些限制。如果您在使用Microsoft的provider时遇到了问题，建议您在洽询Microsoft的技术支持前，先试试Oracle的provider！

 

下一个步骤则是执行sp_addlinkedsrvlogin 建立一个连结服务器之登入帐号。SQL Server 与其它Oracle 客户端一样，也是需要帐号和密码才能登入Oracle Server。

 

您有数种方式可将使用者名称以及密码提供给sp_addlinkedsrvlogin。在下面第一个范例中，我已经事先建立一个名为sys(密码为change_on_install)之登入帐号，以便SQL Server将帐号密码送给Oracle时就能顺利仿真成Oracle的帐号密码。在预设的情况下，Oracle的sys使用者帐号其密码为change_on_install。

因为两个数据库的使用者帐号与密码完全一样，所以SQL Server的登入资格(credential)可以仿真为Oracle的登入资格。在此范例中不需要再对应其它SQL Server的登入资格，所以我将第二个参数值设定为true，表示将利用仿真登入资格的方式来代替：

sp_addlinkedsrvlogin 'OraDB', true, 'sys'

在下面第二个范例，Windows 2000的Administrator帐号将对应到Oracle的scott使用者帐号，其密码为tiger：

sp_addlinkedsrvlogin 'OraDB',false,'W2000AS\Administrator', 'scott', 'tiger'

 

最后一个范例则是让所有SQL Server的登入帐号都以scott的身分，密码为tiger登入Oracle Server：

sp_addlinkedsrvlogin 'OraDB', false, NULL, 'scott', 'tiger'

 

三个之中的后两个范例传递特定的登入资格；因为都没使用到身分仿真方式，所以第二个参数为false。

下图6展示了连结服务器属性对话框内之安全性设定页面。您可以从Enterprise Manager内的Security数据夹开启该对话框。

图6：连结服务器之安全性设定对话框。

 

从SQL Server查询Oracle数据库的资料

您可以利用Oracle的SQL*Plus或是其它协力厂商的免费软件来查询SCOTT的资料，例如Quest Software的TOAD。如下图7所示：

图7：Quest Software的TOAD软件。

(TOAD可由http://www.toadsoft.com下载)

以Microsoft的标准来看，Oracle的工具算是比较粗糙的：SQL*Plus 的功能仅类似于SQL Server的osql命令提示列工具；而TOAD则相当于SQL Server 2000 Query Analyzer与Object Browser。激活SQL*Plus的方式为：点选【开始】，【程序集】，【Oracle】，【Application Development】，【SQL*Plus】。如下图8所示，使用的密码为tiger：

图8：SQL*Plus的登入窗口。

 

请注意所有输入值都是不分大小写的。在SQL>提示符号旁输入以下查询指令：

SELECT * FROM dept WHERE deptno = 10;

您将得到以下查询结果：

DEPTNO DNAMELOC

---------------------------------------

10ACCOUNTINGNEW YORK

 

现在，让我们试着从SQL Server的Query Analyzer透过连结服务器执行上述指令

SELECT * FROM oradb..scott.dept WHERE deptno = 10

 

虽然这道查询指令内的table名称必须是SQL Server所认可的名称(由4个部分组成。译者注三)才能被成功地解析，但第二部分的catalog名称在这里可以不设定。因为所有Oracle的系统资料都是统一集中存放在SYSTEM系统资料表内。

 

译者注3：SQL Server所核可的对象名称由四个部分组成，格式为：

服务器名称.数据库名称.拥有者名称.对象名称。

 

执行上述指令之后将得到以下错误讯息：

 

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

OLE DB provider 'oradb' does not contain table '"scott"."dept"'.

The table either does not exist or the current user does not have

permissions on that table.

 

会得到这个错误讯息是因为Oracle资料字典是区分大小写的。而Oracle的数据库对象名称预设为全部以大写表示。所以透过连结服务器查询时，资料的大小写必须精确地对应到Oracle数据字典内所参照的数据。例如schema名称或是table名称。所以查询指令应该更改为以下方式才能执行：

SELECT * FROM oradb..SCOTT.DEPT WHERE deptno = 10

 

为了避免类似的错误情况发生，您可以为连结服务器上所使用到的table建立一个检视表，查询资料时就可不分大小写！在SQL Server上建立查询Oracle table的检视表的方式如下：

 

CREATE VIEW dbo.v_oradb_scott_dept

AS

SELECT *

FROM oradb..SCOTT.DEPT

 

查询该检视表时，您会发现Oracle系统资料会区分大小写的问题将不再影响到SQL Server的查询指令。这个方便的技巧是透过付费方式取得的。

然而，如果查询指令有包含WHERE子句，则SQL Server会在本地端解析WHERE子句之合法性。因为Oracle并没有接收到WHERE子句，所以在Oracle Server上会进行完整资料表搜寻(full table scan)，并将整个table的资料经由网络传回至SQL Server。

SQL Server 2000 提供了另外一种方法来建立检视表。所谓的Inline表格式使用者自订函数(Inline table-valued user-defined functions，UDFs) 就可实现”可参数化检视表”之功能。您可以像之前使用检视表的方式来查询使用者自订函数，但是使用者自订函数还可以接受参数传递，这项功能是检视表做不到的！

 

您可参考下列方式建立一个使用者自订函数：

CREATE FUNCTION fn_oradb_scott_dept (@deptno int)

RETURNS TABLE

AS RETURN( SELECT * FROM oradb..SCOTT.DEPT

WHERE deptno = @deptno)

如下列指令所示：当您利用这个函数进行查询，不再使用检视表时，WHERE子句将会在远程Oracle Server上进行语法解析。

SELECT * FROM fn_oradb_scott_dept(10)

上述方法和检视表比较起来将带给您较佳的效能表现，并且可有效减少网络上的资料流量。

 

修改连结服务器上的资料

现在让我们试着从SQL*Plus执行一道简单的INSERT指令。注意，这跟在SQL Server执行时有点不同，Oracle的INSERT指令是使用INSERT INTO的语法：

INSERT INTO dept (deptno) values (1);

 

假如您是在Query Analyzer内执行下列指令：

INSERT INTO oradb..SCOTT.DEPT (deptno) values (2)

 

您将会得到下列错误讯息：

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

OLE DB provider 'MSDAORA' could not INSERT INTO table

'[oradb]..[SCOTT].[DEPT]' because of column 'DNAME'. The

column used the default value.

[OLE/DB provider returned message: Multiple-step operation

generated errors. Check each status value.]

 

这个范例就说明了Microsoft OLE DB provider的限制。

DEPT table内含有三个字段：deptno，dname，与loc。其中dname 与loc 两字段是允许NULL值存放的，所以在INSERT指令中并不一定要设定其字段值。

 

但在执行INSERT指令时，如果这些允许NULL值存放的字段并没有给定任何资料，Microsoft OLE DB provider是不会对其做适当地调整且自动设定为NULL！但是您可以用一个简单的方式来处理这种情况，例如在可允许NULL值存放的字段中手动给定NULL值：

INSERT oradb..SCOTT.DEPT (deptno,dname,loc) values (2,NULL,NULL)

 

还有一点需要注意：从SQL Server将资料新增至Oracle table时，不需要包含INTO关键词。Microsoft和Oracle的OLE DB providers在Oracle Server上执行INSERT 指令时都会自动加上INTO关键词。

 

我使用Oracle Provider for OLE DB建立了一个新的连结服务器，名称定为orclprvdr。并尝试对该连结服务器执行INSERT指令，如下所示：

 

INSERT orclprvdr..SCOTT.DEPT (deptno) values (3)

 

这个指令是可以被成功执行的。所以您可以发现：在透过连结服务器执行INSERT指令时，Oracle的provider提供较多的优点！然而，如果table内某字段有设定DEFAULT约束条件，则使用Oracle的provider会无法执行INSERT指令加入数据。不同的是，如果您使用Microsoft的provider，只要传递实际的字段值，而非使用DEAULT关键词，则INSERT指令就可以执行。

 

 

译者注4：这个查询并没有特别设定dname，loc字段为NULL值，但仍旧可以执行。如果是使用Microsoft OLE DB provider for Oracle就必须给定其字段值。

 

UPDATE指令使用在可允许NULL值存放的字段上就不会有问题，这点和INSERT指令不同。如果是对Oracle执行UPDATE指令，不想修改的字段可以不用列出：

UPDATE oradb..SCOTT.DEPT set deptno = 3 WHERE deptno = 2

 

如果您需要针对SQL Server与Oracle的table进行联集(join)查询，就必须要有连结服务器才能执行这类的异质性查询。为了建立下面的范例程序，我使用了资料转换服务(Data Transformation Services，DTS) 将Northwind数据库内的Orders table复制一份到Oracle数据库内的SCOTT schema之下，并且将预设的目的地从 "SCOTT"."Orders" 改成 "SCOTT"."ORDERS"。我在这里使用大写子母的原因是为了避免在Oracle工具内使用到该table时还要加上双引号。虽然Oracle数据库有支持大小写混合式数据库对象名称，但是全部使用大写来命名对象名称的话，对于Oracle数据库管理以及程序开发将会变得较为容易！

 

下列范例将针对Northwind 数据库的Employees table 与SCOTT的Order table

进行联集查询：

SELECT lastname FROM employees e

INNER JOIN oradb..SCOTT.ORDERS o

ON e.employeeid = o.employeeid

WHERE o.orderid = 10248

 

如果您所执行的这类查询指令必须置于一个交易(transaction)内，则SQL Server所在的服务器必须激活交易协调器(Microsoft Distributed Transaction Coordinator (MS DTC)这项服务。

 

以下提供数种使用连结服务器进行查询的方式。例如SQL Server的OPENQUERY 系统函数会将查询指令整个从SQL Server传递至Oracle：

SELECT *

FROM OPENQUERY (oradb,' SELECT * FROM dept

WHERE deptno = 10' )

 

使用OPENQUERY并不会在查阅Oracle系统资料(metadata)时造成额外影响。有了OPENQUERY这个函数，所有查询动作都会发生在Oracle连结服务器上，可避免查询系统资料所可能增加的系统负担。使用OPENQUERY来执行连结服务器的查询指令是一项较佳的选择，但是您无法在异质性的查询指令中使用！

 

至于OPENROWSET 则是一种不需要事先设定连结服务器就可以执行的一种转嫁查询(pass-through query)。除了您必须指定联机时需要的所有资料作为输入参数之外，其功能与OPENQUERY函数类似：

SELECT *

FROM OPENROWSET (' MSDAORA','Ora817'; 'SCOTT';'TIGER',

' SELECT * FROM dept

WHERE deptno = 10' )

 

本文所提到的连结服务器只用来执行查询指令，但是您也可以透过连结服务器执行预储程序。

 

 

注意事项

在您有效地设定连结服务器之前，您必须了解Oracle与SQL Server之间资料型态的差异。如果您将历史资料同时存放在Oracle与SQL Server，则需注意Oracle之date资料型态可存放至纪元前4712年1月1号，但SQL Server的datetime资料型态所存放的资料是从公元1753年1月1号开始。除此之外，Oracle的date资料型态可存放至0.01秒，而SQL Server之datetime资料型态则可存放至0.001秒。如果想得到更多有关SQL Server datetime资料型态的相关信息，请参阅Kalen Delaney的著作，Inside SQL Server。如果您想获取更多有关Oracle资料型态的相关资料，请到http://technet.oracle.com。您可以在该网站免费注册后获取Oracle之文件资料。

 

某些时候您可能需要从多个数据库查询资料，甚至是从另外一台数据库服务器！当其它的数据库为Oracle时，这工作将会变得较具挑战性。Microsoft本身并不打算针对Oracle数据库使用者提供这类顾客服务，而Oracle也不会为SQL Server使用者提供支持。然而，连结服务器却可以方便您从SQL Server存取Oracle数据库的资料。现在您已经了解如何组态并使用Oracle连结服务器，您可以身处于较自在舒服的SQL Server环境，而实时地存取Oracle数据库！