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.
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.
This article was written using Oracle 9i Enterprise Edition
Release 220.127.116.11.0 on Windows 2000 Server as the target, with Microsoft SQL 2000
Developer Edition on Windows 2000 Professional as the parent.
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
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
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
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.
(ADDRESS = (PROTOCOL = TCP)(HOST = infoora)(PORT = 1521))
(SERVICE_NAME = INFOORA.PCC.INT)