Oracle Provider for OLE DB

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