Oracle Provider for OLE DB

The Oracle Provider for OLE DB (8.1.7.3.0) allows distributed queries on data in Oracle databases. Support for the Oracle Provider for OLE DB is as follows:

Oracle 32-bit	Out-of-proc only
Oracle 64-bit	None

To create a linked server to access an Oracle database instance

1. Make sure the Oracle client software on the server that is running Microsoft SQL Server is at the level required by the provider. The Oracle Provider for OLE DB (8.1.7.3.0) requires Oracle Client 8.1.7.0.

2. Create an alias name on the server that is 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 OraOLEDB.Oracle as provider_name, and the alias for the Oracle database as data_source.

   The following example assumes that the alias has been defined as DQORA8:

   exec sp_addlinkedserver @server='OrclDB', 
       @srvproduct='Oracle',
       @provider='OraOLEDB.Oracle', 
       @datasrc='DQORA8'
   

   Security Note:
   This example does not use Windows Authentication. Passwords will be transmitted unencrypted. Passwords may be visible in data source definitions and scripts that are saved to disk, in backups, and in log files. Never use an administrator password in this kind of connection. Consult your network administrator for security guidance specific to your environment.

4. 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 by using the Oracle login and password OrclUsr and OrclPwd:

   sp_addlinkedsrvlogin @rmtsrvname = 'OrclDB', 
      @useself = 'false', 
      @locallogin = 'Joe', 
      @rmtuser = 'OrclUsr', 
      @rmtpassword = 'OrclPwd'
   

Use the following rules when you reference 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.

• This provider does not support the direct passing of datetime literals as arguments in the following format:

  EXEC 'string' '<datetime-literal>' AT <oraclelinkedserver>
  

  For example, the following use of the datetime literal '14-sep-94' is not supported:

  EXEC('INSERT INTO DYNORCL.SALES(ID, ORD_NO, ORD_DATE, QTY) 
     VALUES (?, ?, ?, ?)', '6380', '6871', '14-sep-94', 5) AT OrclDB
  

  Instead, create a datetime variable, assign it the literal value, and call EXEC with the variable as the argument:

  DECLARE @v1 datetime
  SET @v1 = CONVERT(datetime,'14-sep-94')
  EXEC('INSERT INTO DYNORCL.SALES(ID, ORD_NO, ORD_DATE, QTY) 
     VALUES (?, ?, ?, ?)', '6380', '6871', @v1, 5) AT OrclDB
  

Restrictions

SQL Server does not support the use of the AVG aggregate function against Oracle tables because the Oracle provider returns the OLE DB type DBTYPE_VARNUMERIC. SQL Server does not support DBTYPE_VARNUMERIC. For example, the following code returns an error:

SELECT AVG(QTY)
FROM ORA..RPUBS.SALES

To avoid the error, use OPENQUERY. For example:

SELECT *
FROM OPENQUERY(ORA,
   'SELECT CAST (AVG(QTY) AS numeric)
   FROM ORA..RPUBS.SALES')

Registry Entries

To enable the OLE DB Provider for Oracle to work with 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

See Also

Concepts

OLE DB Providers Tested with SQL Server
Distributed Queries

Other Resources

sp_addlinkedserver (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	New content: Added workaround for the restriction on the AVG aggregate function.
5 December 2005	New content: Inserted Security note.