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, see the SQL Server ODBC driver documentation. Refer to the documentation to see 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 username and password for the target SQL Server database.

Notes