Linked Severs on MS SQL Part 4, Oracle

Tuesday Jan 6th 2004 by Don Schlichting
Share:

Part 4 of this series deals with using Oracle as a Linked Server target from MS SQL, and is written from a Microsoft product point of view, assuming familiarity with MS SQL, and relatively little experience with Oracle.

Introduction

In previous articles, we created Linked Servers to Excel, Access and MS SQL. Several stored procedures were introduced for creating links, and the security profiles that accompany them. For this article, sp_AddLinkedServer and sp_AddLinkedSrvLogin should be familiar. Several different access methods have been introduced, primarily Open Query and Direct Access, as well as the different security configurations used to connect with them such as Windows Integrated Security and SQL security.

This article will be concerned with using Oracle as a Linked Server target from MS SQL. Because the previous articles in this series have been written from a Microsoft product point of view, we will continue this way assuming familiarity with MS SQL, and relatively little experience with Oracle.

Oracle Confusion

From the number of forum and news group posts with questions on SQL to Oracle linking, there appears to be a high level of Oracle confusion. A couple of main reasons seem to be responsible for this.

Basic Oracle terms, like database and instance, are different from what the MS SQL DBA would expect, leading to syntactical errors. The other unusual piece is the fact that third party software is required to complete the link. An Oracle client, not available from Microsoft, is required. BOL tells you that it is needed, but because it is from a third party, it does not tell you what to do with it.

Software

This article was written using Oracle 9i Enterprise Edition Release 9.2.0.1.0 on Windows 2000 Server as the target, with Microsoft SQL 2000 Developer Edition on Windows 2000 Professional as the parent.

Oracle Client

The first step in an Oracle Linked Server is to obtain the Oracle Client. SQL will hand off all Oracle information requests to this client. The path is SQL, to the Oracle Client, to Oracle. Then back from Oracle, to the Oracle Client, to SQL.

If you have the Oracle media, you are all set. If not, it can be downloaded from Oracle at http://otn.oracle.com/software/products/oracle9i/index.html. Joining the free Oracle Technology Network may be required.

Once you have the media, run setup.exe to start the Oracle Universal Installer. If space permits, install the "Runtime" version. In addition to the required Oracle Client, some helpful tools will be installed.

Net Manager

With the client installed, go to Programs, Oracle - OraHome92, Configuration and Management Tools, Net Manger. The purpose of the Net Manager will be to create an Oracle server name mapping. Think of this as WINNS, or an Oracle DNS host file creation tool. The end result of the Net Manger will be a file called TNSNames.ORA. The Oracle client will use the information in this file to find the correct Oracle server when SQL gives it a server name.

From within Net Manager, highlight Service Naming, and then click the plus sign on the left. The first dialog will ask for the service name. This will be the alias name that SQL uses for Oracle. In this example, we will call it OracleTest

For Page 2, select TCP/IP as the protocol.

On page 3, the host name is the standard Windows machine name. Port Number is usually 1521. Net Manager on the Oracle server can verify the port and protocol. Check under Local, Listeners.

On Page 4, the real Oracle Service Name is needed. This is a Global Database Name consisting of the Oracle database and domain names. An Oracle database is the equivalent of an SQL instance. In my case, the database is InfoOra and the domain is PCC.INT. Making the Service Name INFOORA.PCC.INT.

If you are unsure of the service name and have access to an Oracle Enterprise Manger, under databases, navigate to the correct database and right click the name. Select "View, Edit Details." At the bottom of the general tab that will appear on the left, click "All Initialization Parameters." There will be an entry for "service_names." This is the correct service name needed.

Page 5 is the test. You should get a successful result. There are two main reasons you may not: a configuration error on pages 1 through 4, or the Oracle user "Scott" with password "tiger" is incorrect. This user is the Oracle equivalent to SQL pubs or Northwind, with an accompanied built in user name. If the user name is the problem, use the "Change Login" button on page 5 and try the test again.

From the top menu, select File, Save Network Configuration.

Here is our end result:

Through the GUI, a file called TNSNames.ORA was created in the Oracle directory of the client. This file can also be made by hand and placed into the Ora92\network\admin directory.

ORACLETEST.PCC.INT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = infoora)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = INFOORA.PCC.INT)
    )
  )

Create the Linked Server

With the Oracle client created, the linked server can now be created. Start Query Analyzer as SA in master. Execute Add Linked Server stored procedure:

exec sp_addlinkedserver 'OracleTest', 
  'Oracle', 'MSDAORA', 'OracleTest'

The first OracleTest will be the SQL name for the linked server. The second is the name that will be passed to the Oracle client.

Next, we need to create a security mapping.

exec sp_addlinkedsrvlogin 'OracleTest', 
  false, 'sa', 'scott', 'tiger'

Scott is a sample user created when Oracle is first installed. Repeat the proc for additional user mappings. The security examples used in the previous articles apply equally for Oracle linked servers.

Testing the Linked Server

Before we can test the linked server from Query Analyzer, a Schema and table name must be known. In Oracle, tables live in a Schema, not in a database. A Schema is owned by, and named after a user. The Schema and table names can be viewed from inside Enterprise Manager. Navigate to the new linked server, and click the Tables icon.

In this example, the Emp table belongs to the Scott Schema. To select this table from Query Analyzer, run:

SELECT * FROM OracleTest..SCOTT.EMP

The Emp table should be returned.

The same options and performance penalties from the previous articles still apply. Therefore, openQuery is still a good idea.

SELECT * FROM OPENQUERY(OracleTest, 'SELECT * FROM SCOTT.EMP')

Conclusion

Linked servers are a convenient way to provide access to various external data sources. The syntax for using them is relatively painless considering the power they offer. The next steps up from linked servers are Distributed Transactions, Two Phase Committing and Partitioned Views. A solid understanding of Linked Servers provides the foundation for all of these advanced topics.

Thanks to all who sent email with questions and suggestions for this series. I did not respond to a couple emails. Unfortunately, those emails became collateral damage during an over zealous spam purge.

» See All Articles by Columnist Don Schlichting

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved