OLE DB Provider for Oracle

Accessing and Changing Relational Data

previous page next page
Accessing and Changing Relational Data

OLE DB Provider for Oracle

The Microsoft OLE DB Provider for Oracle allows distributed queries to query data in Oracle databases.

Note  Oracle client software does not support distributed queries using Microsoft OLE DB Provider for Oracle when the distributed transactions are executed from an instance of SQL Server installed on Microsoft Windows® 98.

To create a linked server to access an Oracle database instance

  1. Ensure the Oracle client software on the server running SQL Server is at the level required by the provider. The Microsoft OLE DB Provider for Oracle requires Oracle Client Software Support File version 7.3.3.4.0 or later, and SQL*Net version 2.3.3.0.4.

  2. Create an SQL*Net alias name on the server running SQL Server that points to an Oracle database instance. For more information, see the Oracle documentation.

  3. Execute sp_addlinkedserver to create the linked server, specifying MSDAORA as provider_name, and the SQL*Net alias name for the Oracle database instance as data_ source.

    This example assumes that an SQL*Net alias name has been defined as OracleDB.

    sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB'
  4. Use sp_addlinkedsrvlogin to create login mappings from SQL Server logins to Oracle logins.

    This example maps the SQL Server login Joe to the linked server defined in Step 3 using the Oracle login and password OrclUsr and OrclPwd:

    sp_addlinkedsrvlogin 'OrclDB', false, 'Joe', 'OrclUsr', 'OrclPwd'

Each Oracle database instance has only one catalog with an empty name. Tables in an Oracle linked server must be referenced using a four-part name of the form OracleLinkedServerName..OwnerUserName.TableName. For example, this SELECT statement references the table SALES owned by the Oracle user MARY in the server mapped by the OrclDB linked server:

SELECT *
FROM OrclDB..MARY.SALES

Use these rules when referencing tables in an Oracle linked server:

  • If the table and column names were created in Oracle without quoted identifiers, use all uppercase names.

  • If the table and column names were created in Oracle with quoted identifiers, use the same case for all letters of the names as was used when the names were created in Oracle.

  • INSERT statements should supply values for all columns in a table even if certain columns in the table can be NULL or have default values.
Registry Entries

To enable the OLE DB Provider for Oracle to work with your Oracle client software, the client's registry must be modified by running a registry file from a command line. Multiple instances of the client software should not run concurrently. These files are listed in the following table and are located within the same directory structure that contains your Microsoft Data Access Component (MDAC) installation, which typically is in C:\Program Files\Common Files\System Files\OLE DB.

Oracle client Windows NT or 9x Windows 2000
7.x mtxoci7x_winnt.reg mtxoci7x_win2k.reg
8.0 mtxoci80x_winnt.reg mtxoci80x_win2k.reg
8.1 mtxoci81x_winnt.reg mtxoci81x_win2k.reg

previous page start next page