Also from 2010 archives: two posts on hetergenous queries using SQL Server and Oracle. I’d hoped to do a 3rd on general issues that arise when keeping data in a remote system, but haven’t produced it yet…
Querying SQL Server from within Oracle and vice-versa is a messy little corner of the database world. . This post will address the setup needed to query SQL Server from Oracle. The next will approach querying Oracle from SQL Server.
Notes on configuring an Oracle database to query a remote SQL Server database follow. For more detailed information, see the Hetergeneous Connectivity section of the Oracle database documentation. It’s important to know that you can either use the ODBC support that comes with the basic Oracle database license for connecting to 3rd party databases. Or you can pay extra for a specific gateway tailored for your target 3rd party database. The Oracle Database Gateway for ODBC User’s guide, discusses specifics about exactly what support is provided with the default no extra cost gateway. This article discusses the no extra cost ODBC gateway.
Note that the no extra cost option that ships with 11G is called DG4ODBC (database gateway for ODBC). It replaces the HSODBC (heterogeneous services ODBC) program that shipped with 10G and earlier. Also note that I was unable to get DG4ODBC working with Oracle DB release 220.127.116.11.0 on Windows. When I upgraded to 18.104.22.168.0, DG4ODBC worked fine.
Connecting to SQL Server from an Oracle database is easier when the Oracle system runs on Windows. That’s because SQL Server client software must be installed on the Oracle host, as well as an ODBC driver for SQL Server. Installing both is straightforward on Windows. Simply install SQL Server client using the file sqlncli.msi, or run the SQL Server database installer and just select the SQL client choices. Doing either takes care of both the native connectivity and the ODBC driver. On unix you will either have to purchase a commercial ODBC driver for SQL Server which comes bundled with the native connectivity like Data Direct or use some of the open source offerings such as Free TDS for the native connectivity and Unix ODBC for the odbc layer.
After you put the SQL Server connectivity software in place, you then need to create an ODBC data source to your remote SQL Server. On windows, this is done via Control Panel – Administrator Tools – Data Sources (ODBC). On unix, you will be configuring text files specific to the odbc driver software you have installed.
At this point, you can verify if connectivity from your host to SQL Server is working without using Oracle. For example on Windows, click the Test Data Source button within the ODBC DSN set up screen. And on unix, there likely will be a command line utility with which you can test connectivity.
With connectivity in place, the next steps are about describing to Oracle your ODBC data source. These steps will be the same on unix and windows since they take place at the Oracle level.
The first part of describing to Oracle your ODBC source is specifying a database gateway. As stated above, we will illustrate using the no extra cost ODBC gateway. You create an initDG4ODBC.ora file that gets put in the $ORACLE_HOME/hs/admin directory. You’ll need these 3 parameters at a minimum:
# Substitute MYDATASOURCE with whatever you called the SQL Server in your ODBC entry. HS_FDS_CONNECT_INFO = MYDATASOURCE HS_FDS_TRACE_LEVEL = off HS_FDS_SUPPORT_STATISTICS=FALSE
There is another step to telling Oracle about your remote SQL Server. You must create an entry in the listener.ora file. It will reference the initDG4ODBC.ora file. The program name will be DG4ODBC.
(SID_DESC = (PROGRAM = DG4ODBC) (SID_NAME = DG4ODBC) (ORACLE_HOME = C:\oracle\product\11.1.0\db_1) )
Once you’ve installed SQL Server client, ODBC driver and configured the $ORACLE_HOME/hs/network\initDG4ODBC.ora and listener.ora files, you are ready to create a database link that connects to the remote SQL Server. This link can then be referenced by SQL statements to get data.
CREATE public DATABASE LINK MYDATASOURCE CONNECT TO "user" IDENTIFIED BY "pwd" USING 'DG4ODBC';
Querying against this link immediately creates case-sensitivity issues. SQL Server will need the correct case for the fields and tables that you query. For example the query below generates an error.
SQL> select name from sysobjects@MYDATASOURCE; select name from sysobjects@MYDATASOURCE * ERROR at line 1: ORA-00904: "NAME": invalid identifier
But this gets the case correct:
select "name" from sysobjects
You can use Oracle SQL functionality provided the DGODBC can convert it successfully.
For example, this will work:
SQL> select column_name from all_tab_columns@MYDATASOURCE where table_name = 'TBLSTATS';
Oracle has mapped its data dictionary view to the native SQL Server one.
You can also send SQL statements directly to the remote SQL Server without having them checked in Oracle first using the DBMS_HS_PASSTHROUGH pl/sql API. This means your syntax can be in SQL Server T-SQL syntax. You can execute a subset of DDL statements with the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE command.