Accessing ODBC and JDBC Data Sources from Oracle Heterogeneous Services (hsODBC)

This tutorial show you how to connect Oracle on Unix and Linux to an Easysoft ODBC driver by using Oracle’s heterogeneous services (hsODBC). Doing this allows you to exchange data between Oracle and other databases. For example, connecting hsODBC to the Easysoft ODBC-SQL Server Driver enables an Oracle database on a UNIX or Linux platform to read/write data in a remote MS SQL Server database.

Contents

1.0 Introduction

Heterogeneous Services is an integrated component within the Oracle database server. It allows transparent SQL access from an Oracle client to non-Oracle systems as if the non-Oracle system was an Oracle database. By using the Heterogeneous Services ODBC (HSODBC) agent with Easysoft products, this functionality can be extended on non-Windows platforms to include any ODBC or JDBC compliant database.

Important HSODBC is a normally a 32-bit application even when distributed with a 64-bit version of Oracle. You need to use the 32-bit HSODBC with a 32-bit Easysoft ODBC driver. For 64-bit platforms, choose the 32-bit version of the driver even when there's a 64-bit version available. 32-bit drivers will still function correctly on a 64-bit platform.

To check whether you have a 32-bit or 64-bit version of HSODBC, on the Oracle machine, run:

file $ORACLE_HOME/bin/hsodbc

If the command output contains ELF-Class64 (or something similar such as ELF-64 or ELF 64-bit), you have a 64-bit HSODBC. Otherwise, you have a 32-bit version.

If $ORACLE_HOME/bin/hsodbc is not present, contact your Oracle DBA.

If you have already downloaded, installed and licensed a 64-bit Easysoft ODBC driver, but need to use a 32-bit version, make a backup copy of /usr/local/easysoft/license/licenses and then remove the Easysoft installation directory. For example:

cp /usr/local/easysoft/license/licenses /tmp
rm -rf /usr/local/easysoft

Download and install the 32-bit version of the driver, and then copy your backup licenses file to /usr/local/easysoft/license.

Easysoft ODBC Drivers:

Note When using hsODBC to access SQL Server you may need to add "DisguiseWide=1" to your DSN as hsODBC does not seem to recognise SQL_WCHAR columns. (See 5.2 No rows returned when connecting Oracle hsODBC to MS SQL Server.)

The scope of this document is to provide a synopsis of what you need to set up to connect from SQL*Plus to an ODBC driver on a Unix based system. It assumes that the Oracle database server is configured and the HSODBC components are already installed on the machine. As HSODBC runs on the Oracle server, you will need to start the Oracle listener. This is not run on some Oracle client/server installations.

The process can be broken down into the following sections:

  1. Install and configure the ODBC driver on the Oracle data-server.
  2. Configure within Oracle:
    • Heterogeneous Services (iniths*.ora)
    • Database Listener (listener.ora)
    • Network Client (tnsnames.ora)
  3. Create a database link through SQL*Plus utilising the above.

2.0 Configure the ODBC driver

Whichever Easysoft product you want Oracle to load data from, they all contain the unixODBC driver manager. It is this that Oracle will load to access the ODBC driver. Essentially we are linking HSODBC to unixODBC and the driver manager is then responsible for the ODBC access. The configuration:

SQL*Plus>Oracle Client>HSODBC instance>UnixODBC>ODBC driver>Database

The Easysoft product that you will be installing has full installation instructions either within the product manual or within a 'Getting Started Guide'. For the purposes of this document it will be assumed that the software is installed, a datasource entered into /etc/odbc.ini and a successful test connection has been made to the database from the isql utility found within /usr/local/easysoft/unixODBC/bin. Once the ODBC driver has been set-up, Oracle can be configured to use this connection.

3.0 Configure Oracle

To allow the Oracle server to use HSODBC, you will need to edit three files and restart the Oracle Listener. These are:

All three files reference either entries made in the other files or the datasource entry made within /etc/odbc.ini. Care needs to be taken when configuring these files as an incorrect entry in any of them can lead to a failure to connect.

3.1 Create an init file

On the Oracle server, login to the Oracle account and cd into $ORACLE_HOME/hs/admin. Every instance using HSODBC needs a separate iniths*.ora file. For this generic document, we created the init file inithsconnect.ora. You can name this file to identify the connection on your machine e.g inithsmssql.ora. This init file gives details of the datasource that is to be connected to and the shared object to be linked by HSODBC to provide the link. To provide this information, append the file with the following information pertinent to your system:

#
# 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 = ODBC_DSN 
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = /tmp/hsodbcsql.trc
HS_FDS_SHAREABLE_NAME =/usr/local/easysoft/unixODBC/lib/libodbc.so

The information that should be entered is as follows:

Once this information has been entered into the file, save into $ORACLE_HOME/hs/admin.

3.2 Edit listener.ora

Edit the listener.ora. This creates the instance that the tnsnames.ora references on connection through SQL*Plus. It creates a SID_NAME and specifies the Oracle executable to run on connection. The listener.ora is found within $ORACLE_HOME/network/admin. Configuring HSODBC needs the creation of a new SID_DESC within SID_LIST in the SID_LIST_LISTENER. An example looks like:

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

The information that should be entered:

Once entered save the file.

3.3 Edit tnsnames.ora

The final Oracle file to edit is the tnanames.ora, found in $ORACLE_HOME/network/admin. This entry identifies the Oracle server to be attached to and the SID_NAME to be used. A new Oracle definition needs to be created:

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

The information that should be entered:

Once added save the file.

3.4 Start/Restart the listener

Once all the entries have been added to the above files, you will need to restart the listener so that the entries that you have made with the listener.ora are used. Note You may not have been running the Oracle listener service previously but you need it to use hsODBC. To do this:

cd $ORACLE_HOME/bin

./lsnrctl stop
./lsnrctl start

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

Service "PLSExtProc" has 1 instance(s).
 Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this
 service...
Service "hsconnect" has 1 instance(s).
  Instance "hsconnect", status UNKNOWN, has 1 handler(s) for this
  service...

The status unknown is returned by hsodbc services, as the service is not created until used.

You can also run lsnrctl and enter services to get a list and the status of the services.

After adding the tnsnames alias and restarting the listener, a connectivity check is to use tnsping <alias>

# tnsping hsconnectid 
TNS Ping Utility for Solaris: Version 10.1.0.2.0 - 
                                     Production on 27-OCT-2004 14:43:57

Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used parameter files:
/space/oracle/OraHome10db/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = hermod)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)
 (SERVICE_NAME = hsconnectid)))
OK (10 msec)

which should come back with a successful message (as shown above).

Once this has been performed, a database instance can be created and connection attempted.

4.0 Create a Database Link

Connect to SQL*Plus as if connecting to your regular database. Once the prompt is loaded, you need to create a new database link as follows:

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

In the above command, hslink is the name of the oracle database link that is created using the hsconnect oracle entry within the tnsnames.ora. Note that when creating the database link, the database user and password are entered. These are not the operating system user and passwords. Also if the 'hsconnectid' is not within single quotes, errors are returned:

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

Once the database link has been created, 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 tables in ODBC to those in your Oracle database:

select * from table@hslink,dual

If you have any problems configuring HSODBC with any Easysoft product, mail them to .

5.0 Known Problems

5.1 unixODBC on AIX

When you configure and build unixODBC for AIX, you end up with the driver manager shared object inserted into libodbc.a as libodbc.so.1. i.e.

$ 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, some applications (e.g. sqlplus) are built and linked against libodbc.so (no version). You can rectify this as follows:

  1. cd to the directory where unixODBC's libraries are installed e.g. /usr/local/easysoft/unixODBC/lib.
  2. extract the shared object from the archive with:
    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 HSODBC directly at the new shared object by amending the HS_FDS_SHAREABLE_NAME in your init[name].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 link should now work.

5.2 No rows returned when connecting Oracle hsODBC to MS SQL Server

We have discovered that some versions of hsODBC do not seem to recognise SQL_WCHAR, SQL_WVARCHAR and SQL_WLONGVARCHAR columns. If a table contains these column types, hsODBC will just error producing no rows. A workaround for this is to add "DisguiseWide=1" to the DSN in your odbc.ini file.

5.3 Invalid user and or password specified

If you get an error similar to:

ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this 
message:

[Generic Connectivity Using ODBC]DRV_InitTdp: errors.h (2052): ;
[unixODBC][][unixODBC][Easysoft SQI-ISAM Driver]
Invalid user and or password specified.
(SQL State: 28000; SQL Code: 702)
ORA-02063: preceding 2 lines from MYLINK

or basically any error indicating the database username and password is incorrect, you have probably specified them incorrectly when creating the database link above. Although you can view links in the dba_db_links table you cannot see the username and password.

If you set HS_FDS_TRACE_LEVEL to 4, set HS_FDS_TRACE_FILE_NAME to a file in you init*.ora file and try again, you will get a trace output file. If you examine the trace file and locate the SQLDriverConnect call you can see the username and password being used. If this verifies the database username and/or password is incorrect then you need to delete your link and recreate it or change the HS_FDS_CONNECT_INFO in your init*.ora file to specify the UID and PWD (see above). You can delete a link using the syntax:

drop public database link MYLINK

Appendix A: Resources

Article Feedback

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

(* Required Fields)