Accessing SQL Server from Oracle with Database Gateway for ODBC (DG4ODBC)

This tutorial shows how to integrate remote Microsoft SQL Server data with Oracle on UNIX and Linux by using DG4ODBC with an ODBC driver for SQL Server.

Connect Oracle to MS SQL Server

We used our SQL Server ODBC driver with DG4ODBC to connect Oracle to SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014 and SQL Server Express databases. The SQL Server ODBC driver also supports earlier (SQL Server 7.0) versions of the database.

The SQL Server ODBC driver provides the necessary connectivity to link Oracle with MS SQL Server on UNIX and Linux The driver also ensures SQL Server features that are useful in the context of transparent integration are available to you. For example, by handling the prerequisite client configuration for database mirroring (SQL Server’s high availability feature), the driver enables you to maintain SQL Server availability in your data integration solution.

Oracle’s heterogeneous connectivity solution enables client applications to take advantage of Oracle’s underlying security features. The SQL Server ODBC driver also enables your solution to take advantage of parallel features in SQL Server, increasing the transparency of the integration. For example, you secure the Oracle database connection with Oracle Advanced Security. Use the SQL Server ODBC driver’s built-in encryption and data integrity mechanisms to specify the same cryptographic protection for the SQL Server connection.

Access MS SQL Server from Oracle on Linux/UNIX

We used our Linux SQL Server ODBC driver to access Oracle from DG4ODBC on 32-bit CentOS and 64-bit Fedora platforms. The SQL Server ODBC driver should work with DG40DBC on any recent Linux distribution: Debian, Mandrake, Oracle Enterprise Linux (OEL), RedHat, RedHat Enterprise Linux (RHEL), SUSE and Ubuntu (Edgy Eft/Feisty Fawn/Gutsy Gibbon/Hardy Heron). The SQL Server ODBC driver is also available for 32-bit and 64-bit AIX, HP-UX and Solaris platforms.

Contents

1.0 Introduction

DG4ODBC interacts with Heterogeneous Services (a component built-in to Oracle) to provide transparent connectivity between Oracle and non-Oracle systems. DG4ODBC uses an ODBC driver to access the non-Oracle system. DG4ODBC is a replacement for the HSODBC agent shipped with Oracle 10g and earlier.

DG4ODBC is included in the Oracle 11g distribution. You can also download DG4ODBC from the Oracle Technology (OTN) Software Downloads Page.

DG4ODBC supports Oracle 11g and Oracle 10g. Note that Oracle 10g needs a patch to work with DG4ODBC (see http://www.oracle.com/technology/products/gateways/pdf/gateways-desupport-168346.pdf). If you want to connect earlier versions of Oracle to non-Oracle systems through ODBC, refer to Accessing ODBC and JDBC Data Sources from Oracle Heterogeneous Services (HSODBC).

This document describes how to use an ODBC driver to access a non-Oracle system from Oracle through DG4ODBC. The document assumes that the Oracle database server is configured and the DG4ODBC components are already installed on a UNIX based system. (For DG4ODBC installation instructions, see the Oracle Database Gateway Installation and Configuration Guide.) As DG4ODBC uses Oracle Net to communicate with Oracle, you will need to start the Oracle Net listener. This is not run on some Oracle client/server installations.

To connect Oracle to a non-Oracle system through DG4ODBC:

  1. Install and configure the ODBC driver on the machine where DG4ODBC is installed.
  2. Configure Oracle:
    1. Database gateway (init*.ora).
    2. Database listener (listener.ora).
    3. Network client (tnsnames.ora).
  3. Create a database link with SQL*Plus.

2.0 64-bit Versions of DG4ODBC

64-bit versions of DG4ODBC require a 64-bit ODBC driver. If you do not know whether your version of DG4ODBC is 32-bit or 64-bit, log on as your Oracle user and run:

cd $ORACLE_HOME/bin
file dg4odbc

If the command output contains something like "ELF 64-bit LSB executable", your version of DG4ODBC is 64-bit, and you need to use it with a 64-bit ODBC driver. If you are still unsure whether your version of DG4ODBC is 64-bit, please contact the Easysoft support team (), and they will be happy to assist.

64-bit versions of DG4ODBC must be used with a 64-bit ODBC driver that has been built with 64-bit SQLLEN/SQLULEN types. The 64-bit SQL Server ODBC driver and 64-bit Oracle ODBC driver that are available for download on the Easysoft web site are built with 64-bit SQLLEN/SQLULEN types. If you want to use DG4ODBC with another 64-bit Easysoft ODBC driver, please contact the Easysoft support team.

32-bit versions of DG4ODBC require 32-bit ODBC drivers, which you can download from the Easysoft web site.

3.0 Install, Configure and Test the ODBC Driver

Oracle loads the unixODBC driver manager to access the ODBC driver. All Easysoft ODBC drivers include unixODBC. Essentially, we are linking DG4ODBC to unixODBC, and the driver manager is then responsible for the ODBC access. The relevant components are:

SQL*Plus -> Oracle Client -> DG4ODBC instance -> unixODBC -> ODBC driver -> Database

You need to:

  1. Install the ODBC driver on the machine where DG4ODBC is installed.
  2. Add a data source to /etc/odbc.ini that connects to the target database.
  3. Test the data source with /usr/local/easysoft/unixODBC/bin/isql.

After you have done this to ensure that the ODBC connection is working, Oracle can be configured to use the connection.

To access SQL Server from Oracle, we used our SQL Server ODBC driver to set up the prerequisite ODBC connection. These instructions show you how to install the SQL Server driver and create and test an ODBC data source.

  1. Download the SQL Server ODBC driver for your DG40DBC platform. (Registration required.)

    Note

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

    For installation instructions, see the SQL Server ODBC driver User’s Guide or Getting Started Guide. 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 ODBC data source in /etc/odbc.ini that connects to the SQL Server database you want to access from Oracle. For example, this SQL Server ODBC data source connects to the default instance on mymachine, which serves the Northwind database:
    [MY_ODBC_DSN]
    Driver                  = Easysoft ODBC-SQL Server
    Server                  = my_machine
    User                    = my_domain\my_user
    Password                = my_password
    # If the database you want to connect to is the default
    # for the SQL Server login, omit this attribute
    Database                = Northwind
  4. Use isql to test the new data source. For example:
    cd /usr/local/easysoft/unixODBC/bin
    ./isql -v MY_ODBC_DSN

    At the prompt, type "help" to display a list of tables. To exit, press return in an empty prompt line.

4.0 Oracle Configuration

To allow the Oracle server to use DG4ODBC, you need to edit three files and restart the Oracle Listener. The files are:

The init file references the data source that you added to /etc/odbc.ini. Entries that you create in tnsnames.ora are subsequently referenced in listener.ora. Care needs to be taken when configuring these files, as an incorrect entry in any of them can lead to connection failure.

4.1 Create an Init File

On the Oracle server, log in to the Oracle account and cd into $ORACLE_HOME/hs/admin. Every instance using DG4ODBC needs a separate init*.ora file. For this tutorial, we created an init file named inithsconnect.ora. You may want to name the init file to identify the target database. For example, inithsmssql.ora.

The init file specifies the:

For example:

#
# This is a sample agent init file containing the HS parameters that
# are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MY_ODBC_DSN
HS_FDS_SHAREABLE_NAME = /usr/local/easysoft/unixODBC/lib/libodbc.so
# HS_FDS_TRACE_LEVEL = Debug
# HS_FDS_SUPPORT_STATISTICS = FALSE

Where:

ParameterDescription
HS_FDS_CONNECT_INFOThe name of the ODBC data source that you added to /etc/odbc.ini. Note that as DG4ODBC uses the SQLDriverConnect ODBC API, you can actually put any valid ODBC connection string here.

Here are some examples:

MY_ODBC_DSN

The name of an ODBC data source in /etc/odbc.ini. Here, DG4ODBC will call SQLDriverConnect with "DSN=MY_ODBC_DSN;UID=xxx;PWD=yyy;", where xxx and yyy are the user name and password specified when the link is created (see Create a Database Link).

DSN=MY_ODBC_DSN;UID=aaa;PWD=bbb;

As before, except that the user name and password in the created link are overridden with aaa and bbb.

DRIVER={Easysoft ODBC-SQL Server};Server=myhost\\myinstance;
UID=mydomain\\myuser;PWD=mypassword;

A DSN-less connection where all the ODBC driver attributes are specified in the connection string. This example is for the SQL Server ODBC driver.

FILEDSN=/tmp/my_odbc_dsn.dsn

This example uses the ODBC FILEDSN attribute. The attribute value instructs the ODBC driver manager to read the details for this data source from /tmp/my_odbc_dsn.dsn.

HS_FDS_SHAREABLE_NAMEThe location of libodbc.so on your machine. This is the driver manager that DG4ODBC will load to access your ODBC data source.
HS_FDS_TRACE_LEVELThe level of tracing. You should generally leave this commented out, as tracing can degrade performance. However, if you are having problems, HS_FDS_TRACE_LEVEL may be set:
HS_FDS_TRACE_LEVEL = Debug

This setting generates a log file for each SQL*Plus connection. Log files are stored in $ORACLE_HOME/hs/log/odbc_agt_pid.trc, where pid is the process ID.

HS_FDS_SUPPORT_STATISTICSThis setting controls whether DG4ODBC makes statistics calls at the same time as trying to query an ODBC connection. By default, SQL Server does not support this behaviour because it does not allow an application to maintain multiple active statements on a connection.

SQL Server 7.0 or 2000

These versions of SQL Server do not support multiple active statements on a connection. Make sure you uncomment the HS_FDS_SUPPORT_STATISTICS entry:

HS_FDS_SUPPORT_STATISTICS = FALSE

Otherwise, you will get this error:

General error: connection is busy with results of another hstmt

SQL Server 2005 or 2008

The Multiple Active Result Sets (MARS) feature provided by SQL Server 2005 and 2008 allows these versions of SQL Server to support multiple active statements on a connection. However, by default, MARS is not enabled. To enable MARS for your DG4ODBC/SQL Server connection, add the following setting to your SQL Server ODBC driver data source in /etc/odbc.ini:

[MY_ODBC_DSN]
.
.
.
MARS_Connection = Yes

Leave HS_FDS_SUPPORT_STATISTICS commented out in the DG4ODBC init file:

#HS_FDS_SUPPORT_STATISTICS = FALSE

4.2 Edit listener.ora

The Oracle Listener listens for incoming requests from the Oracle database. For the Oracle Listener to listen for DG4ODBC, information about DG4ODBC must be added to the Oracle Listener configuration file, listener.ora. You need to:

listener.ora is located in $ORACLE_HOME/network/admin. Create a new SID_DESC within SID_LIST in the SID_LIST_LISTENER entry. For example:

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC=
     (SID_NAME=hsconnect)
     (ORACLE_HOME=/space/oracle/OraHome10db)
     (PROGRAM=dg4odbc)
     (ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:
            /usr/local/easysoft/lib)
   )
 )

Where:

VariableValue
SID_NAMEThe name of the init file without the init prefix or ora extension. For example, if you named the init file inithsconnect.ora, enter hsconnect as the SID_NAME.
ORACLE_HOMEThe location of your Oracle home directory.
PROGRAM
dg4odbc

The Oracle program used by this SID.

ENVSThe environment variables that you need to set for the ODBC driver.

Save the file when you have entered this information.

4.3 Edit tnsnames.ora

The final Oracle file to edit is $ORACLE_HOME/network/admin/tnsnames.ora. You need to add an entry that identifies the Oracle server to attach to and the SID_NAME to use. For example:

hsconnectid=
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521))
   (CONNECT_DATA= (SID=hsconnect))
   (HS=OK)
 )

Where:

VariableValue
hsconnectidThe connect descriptor for DG4ODBC. You include this entry when creating the database link with SQL*Plus.

Reference the SID_NAME you created for DG40DBC in the connect descriptor. For example, "hsconnectid". Do this so that your tnsnames.ora and listener.ora entries can be attributed to each other.

ADDRESSThe entry for your Oracle server. For example:
ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521)
CONNECT_DATAThis must specify the SID_NAME entry you created in listener.ora. For example:
CONNECT_DATA=(SID=hsconnect)
HS=OKAny tnsnames.ora entry that connects to a SID that runs DG4ODBC must include this setting.

Save the file when you have entered this information.

4.4 Start/Restart the listener

You need to restart the listener so that it uses the new listener.ora entries. To do this:

cd $ORACLE_HOME/bin

./lsnrctl stop
./lsnrctl start

Note You may not have been running the Oracle listener service previously, but you need it to use DG4ODBC.

When you restart the listener, you may get information messages similar to:

Service "hsconnect" has 1 instance(s).
 Instance "hsconnect", status UNKNOWN, has 1 handler(s) for this  service...
The command completed successfully

The status UNKNOWN is returned by DG4ODBC, as the service is not created until it is used.

You can also run lsnrctl and enter SERVICES to display service status information.

After adding the tnsnames alias and restarting the listener, use tnsping <alias> to check that you can connect to the new service. If tnsping succeeds, you will get a message similar to:

[oracle@fedora11 ~]$ tnsping hsconnect

TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 06-MAY-2008 17:19:20
Copyright (c) 1997, 2007, Oracle.  All rights reserved.

Used parameter files:
/home/oracle/2/product/11.1.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=fedora11)(PORT=1521))
 (CONNECT_DATA= (SID=hsconnect)) (HS=OK))
OK (0 msec)

If tnsping successfully connects, you can now create a database instance and try to connect to it.

5.0 Create a Database Link

Connect to SQL*Plus as if connecting to your regular database. At the SQL prompt, create a new database link:

CREATE PUBLIC DATABASE LINK hslink CONNECT TO
"dbuser" IDENTIFIED BY "dbpassword" using 'hsconnectid';

The previous command creates an Oracle database link named hslink that references the tnsnames.ora entry you created for DG4ODBC. Note that when creating the database link, the database user and password need to be supplied.

For SQL Server, "dbuser" and "dbpassword" must be a valid SQL Server login and password. For example, "mywindowsuser" and "mywindowspassword" if you normally connect to the instance with a Windows user account or "mysqlserveruser" and "mysqlserverpassword" if you normally connect with a SQL Server account.

'hsconnectid' is the tnsnames.ora entry you created to identify DG4ODBC and must be enclosed in single quotes. Otherwise, you get this error:

ERROR at line 1:
ORA-02010: missing host connect string

After creating the database link, try passing in an SQL statement:

select * from dual@hslink;

where "dual" is a table in your "hslink" linked database.

When passing in SQL to the newly created instance, the syntax is always:

table@linkname

You can even join linked database tables to those in your Oracle database:

select * from table@hslink,dual

If you have any problems configuring DG4ODBC for use with Easysoft ODBC drivers, please contact the Easysoft support team ().

6.0 Known Issues

Unicode Data

If you are using a Unicode ODBC driver such as the SQL Server ODBC driver, ensure these parameters and values are present in your DG4ODBC init file:

HS_LANGUAGE = language_territory.code_page
HS_NLS_NCHAR = UCS2

Replace language, territory and code_page with the Oracle language, territory and code page that correspond with your databases's language, locale and code page.

For example, if your SQL Server language and code page were English (United States) and 1252, your HS_LANGUAGE parameter would look like this:

HS_LANGUAGE = ENGLISH_AMERICA.WE8MSWIN1252

The HS_NLS_NCHAR parameter value tells DG4ODBC to pass UCS-2 encoded data to the Unicode ODBC APIs, rather than UTF-8, which is the default for DG4ODBC. The unixODBC Driver Manager does not support UTF-8 encoded data being passed to the Unicode ODBC APIs.

Ensure also that you have set NLS_LANG before starting your Oracle application. NLS_LANG lets Oracle know what character set your client machine is using. For example:

$ echo $LANG
en_US.UTF-8
$ NLS_LANG=AMERICAN_AMERICA.AL32UTF8 ./sqlplus

If you are using an ANSI ODBC driver, you need to specify a non UTF-8 character set in the HS_LANGUAGE value. For example, HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1. If you do not do this and your Oracle database's NLS_CHARACTERSET value is set to a UTF-8 encoding, for example AL32UTF8, DG4ODBC:

SQL Server varchar(max) Data

By default, the SQL Server ODBC driver exposes the varchar(max) data type as a SQL_VARCHAR. When reporting the maximum size of a varchar(max) column, the driver returns 0, which is the Microsoft convention for "unlimited". DG4ODBC is unable to interpret a zero length as an "unlimited" size and returns an error when retrieving varchar(max) data. To work around this:

  1. Add this line to your SQL Server ODBC driver data source in /etc/odbc.ini:
    VarMaxAsLong = Yes

    When this line is present, the SQL Server ODBC driver returns a varchar(max) column as a SQL_LONGVARCHAR rather than a SQL_VARCHAR with a zero length.

  2. Restart your Oracle listener.

Connection to SQL Server ODBC Driver Fails with ORA-28500 Error

If you get the error:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                [

when trying to connect to your SQL Server ODBC data source, follow these steps:

  1. Add these lines to your init*.ora file:
    HS_NLS_NCHAR = AMERICAN_AMERICA.US7ASCII
    HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII
  2. Stop and start the Oracle listener.
  3. Try reconnecting to your data source.

This error occurs when the Oracle database character set (NLS_CHARACTERSET parameter value) is AL32UTF8. (To check what the database character set is, you can run this query select * from nls_database_parameters;)

Working with DOUBLE or REAL Values

If you have trouble retrieving data when working with DOUBLE or REAL values:

  1. Add this line to your init*.ora file:
    HS_FDS_REPORT_REAL_AS_DOUBLE = TRUE
  2. Stop and start the Oracle listener.
  3. Try reconnecting to your data source.

Character Column Lengths

If DG4ODBC reports the wrong length for your character data or blank-pads character values:

  1. Add these lines to your init*.ora file:
    HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL
    HS_NLS_LENGTH_SEMANTICS = CHAR
  2. Stop and start the Oracle Listener.

For more information about this issue, see the Easysoft Knowledge Base article Why does DG4ODBC report the wrong length for my SQL Server character columns?

Oracle SQL Developer Fails with Error "ORA-00600: internal error code" When Retrieving Multiple varchar(max) Columns

We got this error when testing with Oracle SQL Developer 1.5 and 2.1. We were using DG4ODBC with Oracle 11g Release 1 (11.1.0.6) and the Easysoft ODBC-SQL Server Driver on Linux. (We have also got this error when testing Oracle SQL Developer on Windows with Oracle 11g Release 2 and the Microsoft SQL Server ODBC driver.)

The full text of the error we got was:

SQL Error: ORA-00600: internal error code, arguments: [HO define: Long fetch],
[], [], [], [], [], [], []
00600. 00000 -  "internal error code, arguments:
[%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"

We got the error when using a query that retrieved data from two or more varchar(max) columns. (In our Easysoft ODBC-SQL Server Driver data source, we had the line VarMaxAsLong = Yes, see SQL Server varchar(max) Data.)

Oracle’s documentation on this error states that it is generic internal error number for Oracle program exceptions and that the My Oracle Support -> ORA-00600 Lookup tool should be used for more information regarding the specific ORA-00600 error encountered (which, in the error shown earlier, is 00000).

unixODBC on AIX

On AIX, the unixODBC driver manager shared object is inserted into libodbc.a as libodbc.so.1. The following ar command output shows a unixODBC archive that contains libodbc.so.1.

$ ar -X32_64 -tv libodbc.a
rwxr-xr-x   201/1     636475 Aug 20 09:11 2004 libodbc.so.1

libtool creates the driver manager like this on AIX.

Unfortunately, DG4ODBC is built and linked against libodbc.so (no version). To work around this:

  1. cd to the directory where unixODBC’s libraries are installed. For example:
    cd /usr/local/easysoft/unixODBC/lib
  2. Extract the shared object from the archive:
    ar -X32_64 -xv libodbc.a
    x - libodbc.so.1
  3. Rename libodbc.so.1 to libodbc.so:
    mv libodbc.so.1 libodbc.so
  4. Point DG4ODBC directly at the new shared object by amending the HS_FDS_SHAREABLE_NAME value in your init*.ora file to be:
    HS_FDS_SHAREABLE_NAME = /usr/local/easysoft/unixODBC/lib/libodbc.so
  5. Add this line to your .profile file:
    LIBPATH=$LIBPATH:/usr/local/easysoft/lib:/usr/local/easysoft/unixODBC/lib
  6. Restart your Oracle listener. Your database link should now work.

Appendix A: Oracle Database Gateways and Heterogeneous Services

Oracle Database Gateways allow Oracle client applications to access non-Oracle databases. Oracle Database Gateways and Heterogeneous Services (an integrated Oracle database component) work together to present the appearance of a single, local Oracle database, even though the data might be widely distributed.

The Oracle Database Gateway for ODBC (DG4ODBC) enables you to build heterogeneous solutions that transparently integrate ODBC databases with Oracle. ODBC provides a uniform, cross-DBMS, interface to relational databases. ODBC insulates applications from the database by using data access middleware (known as an ODBC driver) to translate the application’s requests into something that the database understands. Because it uses an ODBC driver to link Oracle to the target database, DG40DBC is not tied to a specific database. This flexible architecture enables Oracle to coexist with any database for which an ODBC driver is available.

By using DG4ODBC, you can immediately use your Oracle applications, both from Oracle and third-parties, to access information in an ODBC database as though it was an Oracle database. Applications do not need to be recoded or reconfigured. Because DG4ODBC has no impact on client applications, you can take advantage of its cross-DBMS architecture to integrate different databases without modifying your applications, should your requirements change.

DG4ODBC comes as part of Oracle 11g, at no additional cost, and supports both Oracle 11g and Oracle 10g. To integrate ODBC databases with earlier versions of Oracle, refer to our HSODBC tutorial.

Because DG4ODBC can connect Oracle to any database for which an ODBC driver is available, Oracle refer to DG4ODBC as a generic connectivity agent.

What Oracle’s Heterogeneous Connectivity Solution Does for You

Appendix B: Resources

Article Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)