Connecting Oracle 21c to SQL Server

Use the SQL Server ODBC driver with Oracle's Heterogeneous Services ODBC agent, DG4ODBC, to connect Oracle 21c to SQL Server.

1. Check whether your version of DG4ODBC is 32-bit or 64-bit:

   file dg4odbc

   If the file command's output contains "ELF 64-bit LSB executable," or something similar, DG4ODBC is 64-bit, and you need to use a 64-bit version of the SQL Server ODBC driver.

   Otherwise, download the 32-bit SQL Server ODBC driver for your platform.

2. Install, license, and test the SQL Server ODBC driver on the machine where DG4ODBC is installed.

   For installation instructions, refer to the SQL Server ODBC driver documentation. Refer to the documentation to find out which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH, or SHLIB_PATH depending on the platform and linker).

3. Create a DG4ODBC init file. For example:

   cd $ORACLE_HOME/hs/admin
   cp initdg4odbc.ora initmssql.ora

4. Ensure these parameters and values are present in your init file:

   HS_FDS_CONNECT_INFO = my_sql_server_odbc_dsn
   HS_FDS_SHAREABLE_NAME = /usr/local/easysoft/unixODBC/lib/libodbc.so
   HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1

   Replace my_sql_server_odbc_dsn with name of a SQL Server ODBC driver data source that connects to the target SQL Server database.

5. Add an entry to $ORACLE_HOME/network/admin/listener.ora that creates a SID_NAME for DG4ODBC. For example:

   SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC=
        (SID_NAME=mssql)
        (ORACLE_HOME=oracle_home_directory)
        (PROGRAM=dg4odbc)
        (ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:
               /usr/local/easysoft/lib)
      )
    )
   

   Replace oracle_home_directory with the value of $ORACLE_HOME.

6. Add a DG4ODBC entry to $ORACLE_HOME/network/admin/tnsnames.ora that specifies the SID_NAME created in the previous step. For example:

   MSSQL=
    (DESCRIPTION=
      (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
      (CONNECT_DATA= (SID=mssql))
      (HS=OK)
    )

7. Start (or restart) the Oracle Listener:

   cd $ORACLE_HOME/bin
   ./lsnrctl stop
   ./lsnrctl start

8. Connect to your Oracle database in SQL*Plus.
9. In SQL*Plus, create a database link for the target SQL Server database. For example:

   CREATE PUBLIC DATABASE LINK mssqllink CONNECT TO
   "dbuser" IDENTIFIED BY "dbpassword" using 'MSSQL';

   Replace dbuser and dbpassword with a valid user name and password for the target SQL Server database.

Notes

• The Oracle 21c version of DG4ODBC no longer uses SQLDriverConnect to connect to an ODBC data source. It uses SQLConnect instead, which does not support HS_NLS_NCHAR = UCS2. Likewise, DSN-less connections don't work with SQLConnect.
• If your linked table contains a column named ROWID, your query will fail with the error:

  ORA-02070: database SQLSRV2019 does not support ROWIDs in this context

  To work around this, change the name of the column from ROWID to ROWNUM.