SQL Server and Oracle: Making the Connection

Linked servers can bring your SQL Server and Oracle databases together

Suppose you have a Microsoft .NET-enabled Internet ordering system but your warranty system is a legacy Oracle database application. You don't want to bill your commercial customers when they place orders for warranty replacement parts, so you need realtime answers from your Oracle database. Creating a linked server lets you use SQL Server to query your Oracle database in realtime to find out who your existing customers are.

When your data is distributed across several databases on multiple SQL Servers, linked servers let you run queries distributed across those servers. When all the servers are SQL Servers, configuration is easy, and SQL Server Books Online (BOL) covers everything you need to know. However, if some of your data is on an Oracle database, for example, configuring a linked server brings special challenges. You need to understand that even if you configure an Oracle linked server in SQL Server Enterprise Manager, your SQL Server is a client of your Oracle database. Therefore, you must successfully install and configure Oracle client software on your SQL Server. Oracle provides product support only for Oracle8 and later, so this article assumes that you're running Oracle8 or later. The Oracle Net8 library provides the client software that SQL Server needs.

In Oracle, a schema is the name for the entity SQL Server professionals know as a database. To connect to Oracle, you need to supply the schema name, password, and host string. An Oracle schema is owned by a specific Oracle username, so the schema name is also the username of the user who owns the schema. One Oracle username owns only one schema. You can find out more about the contents of a schema by querying the Oracle data dictionary, as I describe in the sidebar "System Procedures for Obtaining Linked-Server Metadata," page 26. The Oracle host string is also known as a service name or System Identifier (SID). What SQL Server calls an instance, Oracle calls a database. During an Oracle server installation, Oracle Universal Installer (a graphical interface similar to SQL Server's installation program) asks for a SID to use as the name of the Oracle database.

If you install your Oracle instance on a Windows machine and set your SID to Ora817, as in this article's example installation, you'll have a Windows 2000 service called OracleServiceORA817. This service is analogous to the MSSQLSERVER service for SQL Server 2000. For information about the architectural differences between the two products, see the Microsoft article "Migrating Oracle Databases to Microsoft SQL Server 7.0" (http://msdn.microsoft.com/library/techart/oracle2sql.htm).

Setting Up the Oracle Client on SQL Server

A SQL Server instance and its client exchange data through the application-level Tabular Data Stream (TDS) protocol by using a supported network protocol such as TCP/IP or IPX/SPX. Oracle servers and clients use Net8 to exchange data through three components: the Transparent Network Substrate (TNS), an Oracle protocol adapter, and a supported network library. Net8 uses a protocol adapter for translations between TNS and the network library. Net8, a replacement for SQL*Net, uses service names to find servers. The network libraries Net8 supports are TCP/IP, SPX, Named Pipes, Logical Unit Type 6.2 (LU6.2), and Bequeath. TCP/IP and SPX are the network libraries you can use to connect remote clients to Oracle servers. You use LU6.2 for compatibility with the IBM Advanced Program-to-Program Communications (APPC) architecture. Net8 uses the Bequeath network protocol for making connections when an Oracle client is logged in locally to the Oracle server.

For a Net8 client to make a successful connection to an Oracle server, the client must be able to find the service name for the server. Oracle clients can resolve service names by using a local client file called tnsnames.ora (which is analogous to a Windows HOSTS file), the DNS, or an Oracle Names Server. With Oracle9i, Lightweight Directory Access Protocol (LDAP) becomes another service-name resolution option. However, at press time, Microsoft didn't officially support Oracle9i as a linked server.

The Java-based Oracle Universal Installer helps you install Oracle server and client software on both Windows and UNIX systems. On a Windows system, inserting the Oracle installation CD-ROM autostarts Oracle Universal Installer. I recommend selecting a default installation of the Oracle8 client because in addition to installing Net8, the default installs the Net8 Assistant, the Net8 Configuration Assistant, and SQL*Plus and adds these options to your Start menu. SQL*Plus is a client tool comparable to SQL Server's osql.

Troubleshooting

Using SQL Server to validate a linked-server connection to Oracle can give misleading results. Until you execute a linked-server query, you can't be certain whether you configured the link correctly. Keep in mind that you can issue a linked-server query indirectly. In Enterprise Manager, if you select Tables or Views under your linked-server definition, you're indirectly querying the Oracle data dictionary. After I issued such a query, I received the error message in Figure 1. But when I executed Oracle's TNSPING command (similar to a TCP/IP PING, but specifically for testing connectivity to an Oracle database) from a command prompt, the results showed that Oracle client and networking components were installed, as Figure 2 shows.

Additionally, I had successfully used both the Net8 Assistant, as Figure 3 shows, and the Net8 Configuration Assistant to make actual connections to the example SCOTT schema, which is analogous to the SQL Server Pubs database. Using either of those network administration tools is sufficient for validating a database connection, so I knew that the problem lay elsewhere.

When you're creating linked servers to non-Microsoft databases, check the PATH environment variable. Note that Oracle incorrectly appears before SQL Server in the PATH variable that Figure 4 shows. To prevent the error message I received, edit PATH as Figure 5 shows, placing SQL Server ahead of any other vendor you're linking to. After I corrected PATH, the linked queries worked correctly.

Configuring SQL Server

Creating a linked Oracle server on SQL Server 2000 imposes an additional configuration requirement: You must manually add a key to the client machine's registry. (In this case, your SQL Server is the client of the Oracle server.) You need to choose the registry keys for the appropriate version of Windows and your Oracle server. You can find the appropriate registry files, which begin with mtx, in the C:program filescommon filessystemole db folder. Double-click the appropriate registry file to add the necessary key. For more information, see BOL; go to Accessing and Changing Relational Data, Distributed Queries, OLE DB Providers Tested with SQL Server, OLE DB Provider for Oracle, then look under Registry Entries.

You create linked servers by executing a series of two system stored procedures. To begin, you use sp_addlinkedserver to specify your choice of OLE DB provider. The following example specifies Microsoft Data Access for Oracle:

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

Or you can specify Oracle's OLE DB provider:

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

You must pick one or the other of these options. Although Microsoft officially supports and recommends only the Microsoft OLE DB Provider for Oracle, this provider has limitations that I show later in an INSERT example. If you experience problems when you're using Microsoft's provider, try using Oracle's provider before calling Microsoft technical support.

The next step is to use sp_addlinkedsrvlogin to create a login to the linked server. SQL Server is like any other Oracle client in that it must supply a username and password to connect to the Oracle server. You can provide the username and password to sp_addlinkedsrvlogin in several different ways. In the first example that follows, I had already created a SQL Server login of sys with a password of change_on_install to let SQL Server impersonate an Oracle username and password by supplying a SQL Server username and password to Oracle. By default, the Oracle user sys has a password of change_on_install. Because the usernames and passwords for the two database servers match, the SQL Server credentials can impersonate the Oracle credentials. No mapping of SQL Server credentials is required in this case, so I used true as the value of the second parameter, indicating that impersonation of credentials will take place. In the second example, the Win2K user Administrator is mapped to the Oracle user scott with a password of tiger. The last example directs all other SQL Server logins to connect as scott with a password of tiger. The latter two examples pass specific credentials; because they don't use impersonation, the second parameter is false.

sp_addlinkedsrvlogin 'OraDB', true, 'sys'sp_addlinkedsrvlogin 'OraDB', false,    'W2000ASAdministrator', 'scott', 'tiger'sp_addlinkedsrvlogin 'OraDB', false, NULL, 'scott','tiger'

Figure 6, page 28, shows the security settings on the Linked Server Properties dialog box's Security tab. You can access this dialog box from Enterprise Manager by opening Linked Servers in the Security folder.

Executing an Oracle Query from SQL Server

You can query the SCOTT schema by using Oracle's SQL*Plus or a third-party freeware tool such as Quest Software's TOAD, which Figure 7, page 28, shows. (TOAD is available from http://www.toadsoft.com.) Oracle tools are crude by Microsoft standards: SQL*Plus has functionality similar to the osql command-line tool. TOAD, however, is comparable to SQL Server 2000 Query Analyzer and Object Browser. To start SQL*Plus, choose Start, Programs, Oracle, Application Development, SQL*Plus. In the Log On dialog box that Figure 8, page 28, shows, use tiger as the password. Note that all entries are case insensitive. At the SQL> prompt, enter the following query:

SELECT * FROM dept WHERE deptno = 10;

You'll get the following output:

    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     NEW YORK

Now, try executing the above SELECT statement as a SQL Server linked-server query from Query Analyzer. Although this query requires a fully qualified four-part name for resolution, the catalog is always left empty because all Oracle metadata resides in one centralized system catalog called SYSTEM.

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

Executing the above query returns the following error:

Server: Msg 7314, Level 16, State 1, Line 1OLE DB provider 'oradb' does not contain table '"scott"."dept"'.The table either does not exist or the current user does not havepermissions on that table.

You get this error because the Oracle data dictionary is case sensitive. Oracle object names are all uppercase by default. The linked-server query must exactly match the case of all referenced Oracle metadata, such as schema and table names. So, the following query works:

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

To prevent similar errors, you can create a view to give a case-insensitive representation of a linked-server table. You begin by creating a SQL Server view of an Oracle table:

CREATE VIEW dbo.v_oradb_scott_deptASSELECT *FROM oradb..SCOTT.DEPT

By querying this view, you can see that the case sensitivity of Oracle metadata no longer affects SQL Server queries against the data. This convenience comes at a price, however. SQL Server resolves the query's WHERE clause locally. Because Oracle doesn't see the WHERE clause, it does a full table scan and sends the entire table across the network.

SQL Server 2000 offers an alternative to creating a view. Inline table-valued user-defined functions (UDFs) can give you the same functionality as a parameterized view. You can query UDFs as you would a view, but UDFs can accept parameters, which a view can't. You can create the following UDF:

CREATE FUNCTION fn_oradb_scott_dept (@deptno int)RETURNS TABLEAS RETURN (SELECT * FROM oradb..SCOTT.DEPT WHERE deptno  = @deptno)

When you execute this function instead of querying the view, as follows, the WHERE clause is resolved remotely on the Oracle server:

SELECT * FROM fn_oradb_scott_dept(10)

This approach gives you better performance and reduced network traffic compared with using a view.

Updating Linked Data

Now, consider executing a simple INSERT statement from SQL*Plus. Note that unlike SQL Server, Oracle requires the INSERT INTO syntax in every INSERT statement:

INSERT INTO dept (deptno) values (1);

If you execute a similar query from Query Analyzer, as follows,

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

you get the following error message:

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'. Thecolumn used the default value.

[OLE/DB provider returned message: Multiple-step operationgenerated errors. Check each status value.]

This example illustrates a limitation of the Microsoft OLE DB provider. The DEPT table has three columns: deptno, dname, and loc. The dname and loc columns are nullable, so they shouldn't be necessary in an INSERT statement. The Microsoft provider doesn't properly accommodate nullable columns if they're not supplied in an INSERT statement, but you can use a simple workaround. Specify the nullable columns and give them values:

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

Also note that when you're inserting data into an Oracle table from SQL Server, you don't need to include INTO. Both the Microsoft and Oracle OLE DB providers add INTO to the INSERT statement they execute on the Oracle server.

Using the Oracle Provider for OLE DB, I created a new linked server named orclprvdr and attempted a linked-server INSERT, as follows:

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

This query worked, so you can see that Oracle's provider offers advantages when you're doing linked-query INSERT statements. However, Oracle's provider can't execute an INSERT into a table that contains a column that has a DEFAULT constraint defined on it. Microsoft's provider can execute that INSERT as long as you pass an actual value for the column instead of using the keyword DEFAULT.

Unlike an INSERT statement, an UPDATE statement doesn't have problems with nullable columns. If you're issuing a linked-server UPDATE to Oracle, you don't need to specify the nullable columns if you aren't updating them.

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

If you need to join a SQL Server table to an Oracle table, you're executing a heterogeneous query and you must have a linked server. To construct the following example, I used Data Transformation Services (DTS) to move a copy of the Northwind database's Orders table to the SCOTT schema, changing the default destination of "SCOTT"."Orders" to "SCOTT"."ORDERS". I changed the case here to avoid having to use quoted identifiers when I'm using Oracle tools to refer to the table. Oracle databases can support mixed-case object names, but administration and development of an Oracle database is easier when you use all-uppercase object names. The following query joins the Northwind Employees table to the SCOTT schema's copy of Orders:

SELECT lastname FROM employees eINNER JOIN oradb..SCOTT.ORDERS oON e.employeeid = o.employeeidWHERE o.orderid = 10248

If your heterogeneous query needs to be inside a transaction, you must have the Microsoft Distributed Transaction Coordinator (MS DTC) service running on your SQL Server. For more information, see the sidebar "Transactions," page 28.

Here's one more way to execute a query when you're using a linked-server definition. SQL Server's OPENQUERY system-defined function passes the query through from SQL Server to Oracle:

SELECT * FROM OPENQUERY (oradb,'SELECT * FROM deptWHERE deptno = 10')

Using OPENQUERY doesn't incur the overhead of the Oracle metadata lookups that I discuss in the sidebar "Analyzing Linked-Server Queries," page 30. With OPENQUERY, all query processing happens on the Oracle linked server and you avoid the metadata lookups. OPENQUERY performs a linked-server query better, but you can't use it in a heterogeneous query.

OPENROWSET is a pass-through query that doesn't use a linked-server definition. It's similar to OPENQUERY, except that it requires you to provide all connection criteria as input parameters:

SELECT * FROM OPENROWSET ('MSDAORA','Ora817';'SCOTT';'TIGER',' SELECT * FROM dept WHERE deptno = 10')

Linked servers are only for executing queries, but as the sidebar "Executing Stored Procedures Remotely" shows, you can execute a query to execute a stored procedure.

Bear in Mind

Before you can use your linked-server setup effectively, you need to know the differences between Oracle and SQL Server data types. If you're storing historical dates in both Oracle and SQL Server, be aware that Oracle's date data type can store dates back to January 1, 4712 b.c., but SQL Server's datetime data type begins at a.d. January 1, 1753. Additionally, Oracle's date data type has a granularity of 0.01 second and SQL Server's datetime has a granularity of 0.001 second. (For more information about the SQL Server datetime data type, see Kalen Delaney, Inside SQL Server, "Solving the Datetime Mystery," September 2000.) For more information about Oracle data types, go to http://technet.oracle.com. You'll need to register (it's free) to get access to the Oracle documentation.

Sometimes you need to get data from more than one database or even from another database server. And when the other database is Oracle, that task is particularly challenging. Microsoft isn't in the business of providing customer support for Oracle databases, and Oracle isn't in the business of providing customer support for SQL Server. However, linked servers can provide convenient access to Oracle data from SQL Server. Now that you know how to configure and use Oracle linked servers, you can get realtime Oracle data in the SQL Server environment you're comfortable in.