文章多数来自互联网,若有冒犯的地方,请朋友们说明下,我会及时删除该文章!

如何连接SQL Server与Oracle数据库

上一篇 / 下一篇  2011-02-25 16:52:00

如何连接SQLServerOracle数据库

原文出处: 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数据库!


TAG:

 

评分:0

我来说两句

congyu15

congyu15

自动化测试工具学习ING,做了近两年的手工测试,对于自动化测试一知半解。希望同行的兄弟姐妹们能够帮助我、指导我学习自动化测试工具,你们的一字一句都是我成长的源泉。感谢你们的无私奉献、乐此不疲!

日历

« 2021-11-27  
 123456
78910111213
14151617181920
21222324252627
282930    

数据统计

  • 访问量: 19771
  • 日志数: 126
  • 建立时间: 2010-11-24
  • 更新时间: 2012-02-17

RSS订阅

Open Toolbar