Microsoft OLE DB Provider for Oracle (Database Engine)
The Microsoft OLE DB Provider for Oracle allows distributed queries on data in Oracle databases.
To create a linked server to access an Oracle database instance
Make sure 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.
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.
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.
The following example assumes that an SQL*Net alias name has been defined as OracleDB.
sp_addlinkedserver 'OrclDB ','Oracle',' MSDAORA','OracleDB'Use sp_addlinkedsrvlogin to create login mappings from SQL Server logins to Oracle logins.
The following 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 by using a four-part name of the form OracleLinkedServerName**..OwnerUserName.**TableName. For example, the following 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
When you reference tables in an Oracle linked server, use these rules:
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 registry of the client must be modified by running a registry file from a command line. Multiple instances of the client software should not run at the same time. These files are listed in the following table and are located within the same directory structure that contains the Microsoft Data Access Component (MDAC) installation. This is typically located 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 |
mtxoci80_winnt.reg |
mtxoci80_win2k.reg |
8.1 |
mtxoci81_winnt.reg |
mtxoci81_win2k.reg |