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.
- 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.
- 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
orSHLIB_PATH
depending on the platform and linker). - Create a DG4ODBC init file. For example:
cd $ORACLE_HOME/hs/admin cp initdg4odbc.ora initmssql.ora
- 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.
- 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
. - 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) )
- Start (or restart) the Oracle Listener:
cd $ORACLE_HOME/bin ./lsnrctl stop ./lsnrctl start
- Connect to your Oracle database in SQL*Plus.
- 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
- The Oracle 21c version of DG4ODBC no longer uses
SQLDriverConnect
to connect to an ODBC data source. It usesSQLConnect
instead, which does not supportHS_NLS_NCHAR = UCS2
. Likewise, DSN-less connections don't work withSQLConnect
. - 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.