Welcome to the Blog of Mike Ross

« Connecting Oracle 10g on Solaris Sparc 64 to SQL Server on Windows using FREE ODBC Driver | Main | Oracle Database Gets Confused, Then Fixed »

July 23, 2009

Connecting Oracle 10g on Solaris Sparc 64 to SQL Server on Windows using FREE ODBC Driver - Part Two

In Part One of this two-part mini-series we reviewed the setup required to connect to a remote SQL Server database using a unix/Solaris 9 free odbs driver. Now comes the tricky part - making this connection useful. The goal is to be able to call this remote data directly from sql/code within our Oracle 10gR2 database. A noble goal it is....

From the first article, we know we have a working odbc connection. If you don't have this, go back and make sure that much is working before proceeding. This is one of those times when you need to go step by step or you will have very little chance of making the whole thing work. Such things as useful error messages and/or log files aren't available.

In order to complete this project, we need the following:

• Database Link
• listener.ora
• tnsnames.ora
• HS (Heterogeneous Services)

We can start with the HS setup. Oracle has two "things" that connect to odbc drivers - HS (which has the advantage of being free) and Oracle Gateway. I only know about HS, but there is a lot of documentation on Oracle Gateway as well. The Gateway provides a more robust connection environment. In order to get HS going, we go to the hs admin folder which is located at ORACLE_HOME/hs/admin.

Create an initclmd.ora file in the hs/admin directory. You create this config file to match the name you gave to your connection when we first started. For me it is "clmd", so the config file is named initclmd.ora - you get the idea.

initclmd.ora -

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = clmd
HS_FDS_TRACE_LEVEL = 4
HS_FDS_SHAREABLE_NAME = /[your path]/odbc/lib/libodbc.so
HS_FDS_TRACE_FILE_NAME = /[your path]/odbc/tmp/freetds.trc
#
# ODBC specific environment variables
#
set ODBCINI=/[your path]/odbc/etc/odbc.ini

#
# Environment variables required for the non-Oracle system
#
#set =

Fill in your connection name, which you should now know by heart. trace_level - 4, I don't know, it was suggested in the documentation and works.
shareable_name = this is the path to your odbc libraries. Be sure to look in there and verify that you have the libodbc.so file or you are just wasting your time.
trace_file_name = make something up.
ODBCINI = path and name to your odbc.ini file
Save it, you are done configuring HS. Now it knows where to go to find your odbc driver.

Here's the part that always gives me trouble - SQL Net. Here, we need to configure the tnsnames.ora and listener.ora files to get a listener for our requests to the remote database. I wound up stopping and starting the listener quite a few times before getting this part right, but maybe that's just me. I hope that these copies of my config files will help you.

tnsnames.ora - add in a section like this

CLM_Pay = (DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)
(HOST=[name of server with Oracle database on it])(PORT=1521))
(CONNECT_DATA = (SID=clmd)
)
(HS=OK)
)

The name of this tnsnames entry isn't too significant, you just use that same name when configuring the database link. The HOST and PORT should mirror the host name and port number your Oracle database listener uses. Port 1521 is the standard port, but your system might be using a different port. Under the CONNECT_DATA use SID, not SERVICE_NAME. You recognize the now familiar odbc connection name. The HS=OK entry tells Oracle to go look in the initclmd.ora for the connection information. Done here.

back up your listener.ora file (just in case - I needed the original several times...)

listener.ora - putting it all into action

# listener.ora Network Configuration File: /[your OH]/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =[your OH])
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME=clmd)
(ORACLE_HOME=[your OH])
(PROGRAM=hsodbc)
(ENVS=LD_LIBRARY_PATH=/[your OH]/lib32:/[your path]/odbc/lib)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = [local server])(PORT = 1521))

This is by far the most complicated config file. There are a lot of unwritten rules. What we need to do is to add an item to the sid_list_listener= sid_list. I'm showing you the PLSExtProc entry so that you can see the syntax for adding in another "sid_list" item. PLSExtProc has nothing to do with the odbc connection. It is just another listener item we happen to have on our database. So we add a SID_DESC item to the SID_LIST.
SID_NAME = if you don't know it by now, it's hopeless ;-)
ORACLE_HOME= copy paste from your other entries
PROGRAM = hsodbc That, you just need to know (now you do).
ENVS=LD_LIBRARY_PATH= ORACLE_HOME/lib32: /[your path]/odbc/lib
OK, this is important. I have not seen this in any documentation, and would not have gotten this right without Oracle Support's help. First is the syntax for adding two library paths. Just copy mine - it's right. Second, notice the first part of the library path - what is that? 32 bit libraries! This is where you convert your 64 bit database to 32 bit - Oracle has already provided the 32 bit libraries for you, you just have to point to them. Everything else - odbc, freetds, use 32 bit libraries to match the remote database (NOT 64 bit libraries to match your OS/database!). Dave reminded me that the 64 bit OS can read 32 bit libraries (but not vice versa). So all the confusion about 64 - 32 bit libraries comes down to right here. Just have your 64 bit Oracle database call its own 32 bit libraries and use 32 bit libraries everywhere else.

One more thing before leaving this part - only two items should be aligned on the leftmost column of the listener.ora file. The SID_LIST_LISTENER =, and the LISTENER = are the only items that go against the left margin. Everything else must be indented (it's hard to see on my example because they aren't indented very much). Learned that the hard way....

stop and restart the listener. If it restarts without error, you should see your new listener item displayed in the output. Once you get this piece going, you're nearly done.

Create a database link:

create public database link [any name you choose]
connect to useridforremotedatabase identified by remotepassword
using 'CLM_Pay';

This is standard stuff - the using 'xxxxxxxx' part must match whatever you put in the tnsnames.ora entry. Here's the final product:

From your favorite SQL spot:
select * from school_codes@clmd;

RESULT SET:

1 1 WOU 00320900 WO Western Oregon University wou.jpg Dona Vasas N N

Gee, it looks so simple! Well, you know better, but that's often what we work for - making something that's pretty darn complex look easy!

By the way, the second connection you create takes a whole lot less time! We added a connection to a MySQL database in a matter of a few hours. I hope this article will help you get your Oracle database talking to other data sources.

Posted by rossm at July 23, 2009 8:17 AM

Comments

Post a comment