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

Contents

• Introduction
• 64-bit versions of DG4ODBC
• Install, configure, and test the ODBC driver
• Oracle configuration
  • Create an init file
  • Edit listener.ora
  • Edit tnsnames.ora
  • Restart the listener
• Create a database link
• Known issues
  • Unicode data
  • SQL Server varchar(max) data
  • Connection to SQL Server ODBC driver fails with an ORA-28500 error
  • Working with DOUBLE or REAL values
  • Character column lengths
  • Oracle SQL Developer fails with error "ORA-00600: internal error code" when retrieving multiple varchar(max) columns
  • unixODBC on AIX
• Appendix A: Oracle Database Gateways and Heterogeneous Services

Introduction

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.

The SQL Server ODBC driver provides the necessary connectivity to link Oracle with Microsoft 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.

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. Note that Oracle 10g needs a patch to work with DG4ODBC. 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, refer to 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 (initSID.ora).
   2. Database listener (listener.ora).
   3. Network client (tnsnames.ora).
3. Create a database link with SQL*Plus.

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're still unsure whether your version of DG4ODBC is 64-bit, contact the Easysoft support team () who 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 and SQLULEN types. The 64-bit SQL Server ODBC driver and 64-bit Oracle ODBC driver that are available to download on the Easysoft web site are built with 64-bit SQLLEN and SQLULEN types. If you want to use DG4ODBC with another 64-bit Easysoft ODBC driver, contact the Easysoft support team.

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

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 ODBC driver and create and test an ODBC data source.

1. Download the SQL Server ODBC driver for your DG4ODBC platform.

   Note If the SQL Server ODBC driver is not currently available for your DG4ODBC platform, check the list of ODBC-ODBC Bridge client platforms. The ODBC-ODBC Bridge is an alternative SQL Server solution from Easysoft, which you can download from this site.

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

   For installation instructions, refer to the SQL Server ODBC driver User's Guide or Getting Started Guide. 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 an 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
   Override                = 65536

   For more information about the Override setting, refer to this blog.

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, enter help to display a list of tables. To exit, press Return in an empty prompt line.

Oracle configuration

To use DG4ODBC, you need to edit three files:

• initSID.ora
• tnsnames.ora
• listener.ora

The initSID.ora 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 a connection failure.

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 initSID.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:

• ODBC data source to connect to.
• Path of the ODBC Driver Manager shared object.

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 = 4
HS_NLS_NCHAR = UCS2

where:

MY_ODBC_DSN

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

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

FILEDSN=/tmp/my_odbc_dsn.dsn

HS_FDS_TRACE_LEVEL = 4

HS_NLS_NCHAR = UCS2

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:

• Create a SID_NAME for DG4ODBC.
• Specify the executable that the listener should start in response to DG4ODBC connection requests.

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:

dg4odbc

Can't open lib 'lib' : file not found

Can't open lib '/usr/local/easysoft/sqlserver/lib/libessqlsrv.so' : file not found

Save the file when you have entered this information.

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:

ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521)

CONNECT_DATA=(SID=hsconnect)

Save the file when you have entered this information.

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'll get a message similar to:

[oracle@fedora11 ~]$ tnsping hsconnect
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.

Create a database link

In SQL*Plus, 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 mytable@hslink;

where mytable 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 mytable@hslink,dual

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

Known issues

Unicode data

If you're 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 database'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 some versions of 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're 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 don't do this and your Oracle database's NLS_CHARACTERSET value is set to a UTF-8 encoding, for example AL32UTF8, DG4ODBC:

• Uses the Unicode ODBC APIs, for example, SQLDriverConnectW, which the unixODBC Driver Manager maps to their ANSI equivalent (or if your HS_FDS_SHAREABLE_NAME parameter points to the ODBC driver shared object, causes the connection to fail with an "undefined symbol" error).
• Passes UTF-8 encoded data to the ODBC APIs, which the unixODBC Driver Manager does not support. The connection fails because DG4ODBC passes a UTF-8 encoded connection string to SQLDriverConnectW, which the unixODBC Driver Manager is unable to handle.

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 an 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 initSID.ora file:

   HS_NLS_NCHAR = AMERICAN_AMERICA.US7ASCII
   HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII

2. Restart 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 initSID.ora file:

   HS_FDS_REPORT_REAL_AS_DOUBLE = TRUE

2. Restart 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 initSID.ora file:

   HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL
   HS_NLS_LENGTH_SEMANTICS = CHAR

2. Restart the Oracle Listener.

For more information about this issue, refer to 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 SQL Server ODBC 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 SQL Server ODBC driver data source, we had the line VarMaxAsLong = Yes, refer to SQL Server varchar(max) data.)

Oracle's documentation on this error states that it's a 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 initSID.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, DG4ODBC 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 and later, at no additional cost. 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 Services solution does for you

• Integrates remote data transparently Oracle Heterogeneous Services and Database Gateways combine to mask the location of remote non-Oracle data from the application or user by making it appear as one unified Oracle database. The heterogeneous nature of the data is completely transparent. This enables one set of applications to quickly, efficiently, and economically deploy data that may exist in many Oracle and non-Oracle systems to users who can use existing methods to access and update the data.
• Reduces data duplication Oracle applications access data in non-Oracle databases directly. The data remains in its native store rather than being moved or duplicated. This approach removes the risk of redundant or inconsistent data and disruption to existing systems. Application developers can focus on enhancing functionality rather than maintaining and managing copies of the data.
• Protects your investment in Oracle applications Oracle heterogeneous connectivity integrates your existing Oracle applications with non-Oracle databases. You do not need to purchase or develop new applications to access non-Oracle data. Oracle's heterogeneous data sharing solution not only protects your investment in Oracle applications, but also in staff training — there are no new tools for end users to learn how to use or IT staff to support.
• Reduces development costs Oracle's heterogeneous connectivity solution provides a consistent Oracle interface to both Oracle and non-Oracle databases. This results in applications that are easier to code, maintain, and extend because they can be built upon a standard Oracle interface that is independent of the non-Oracle database's native interface.

• About Easysoft

• Contact us
• About us
• Clients
• Blog
• Careers

• Products

• ODBC drivers
• JDBC drivers
• Bridges and gateways
• In development

• Services

• Consultancy
• Training
• Custom development

• Licensing

• Product licenses
• Prices

• Support

• Support home
• Solution wizard
• Getting Started Guides
• User Guides
• Knowledge Base

• Search

• Easysoft.com
• Documentation
• Knowledge Base

• Popular Resources

• Developer area
• Client applications

• Legal

• Privacy statement

© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.