OLE DB Provider for Oracle

The Microsoft OLE DB Provider for Oracle provides access to Oracle databases. Using the Microsoft OLE DB Provider for Oracle, Microsoft® SQL Server™ distributed queries can query data in Oracle databases.

To set up 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 your 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 long 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.