LewisC's An Expert's Guide To Oracle Technology
Have you ever needed to bring data from a non-Oracle databases into your Oracle database? This is very common in a warehouse and is even somewhat common in ERP solutions. For a price, Oracle offers Transparent Gateways to many popular databases. What do you do if you need to load some data from a data source but there is no Transparent Gateway (or if you have no budget for one)? If that data source happens to support ODBC, you're in luck and it won't cost you anything above and beyond what you are already paying for your database.
The solution is called Heterogeneous Services Generic Connectivity. I usually just call it HS links. Basically, you configure an ODBC data source and then create a database link against that source. You can run DML against the source just as you would against an Oracle database.
ODBC is available for most flavors of Unix, Linux and, of course, windows. I believe there is even an ODBC package for Mac but I am not a mac user so I can't say that it works. Any features you want to use must be supported by your ODBC driver.
If you are on a 64 bit Windows OS and you are connecting to a 32 bit database, you MUST use the 32 bit ODBC admin tool (c:\windows\syswow64\odbcad32.exe) versus the 64 bit ODBC admin tool (c:\windows\system32\odbcad32.exe). You can get more details about this on my Postgres blog, 32 Bit ODBC Drivers in Vista 64.
Here is the short story about how to get this installed and working. It's really fairly easy. These instructions are for 10g. They probably work in other versions but I have not specifically ran these instructions through those versions.
NOTE: This configuration is done on the database server, not your client. You must have access to an account on your database server that will let you work with system and oracle configuration files..
Configure ODBC
First, get your ODBC drivers for the database you want to connect to. I will use EnterpriseDB since I just happen to have a database loaded and running already. ;-)
If you have installed Advanced Server, you should already have the odbc drivers installed. I know Ingres and SQL Server also automatically install drivers. MySQL provides a separate download. There are so many different tools available that I can't give you detailed instructions on finding and installing your drivers.
Ok, now that you have ODBC installed, create an ODBC data connection. This is really very simple. In Windows, make sure that you choose to create a System DSN. I will call my DSN "EnterpriseDB". Test your DSN to make sure your information is correct. Windows includes a test button on the form where you create the DSN. Make sure to save the DSN.
That's it as far as playing with ODBC goes but remember the DSN name you just created. You'll use it in several places later on.
Configure Connectivity Agent
The next step is to identify the data source to Oracle. You do this by create an initialization file just like you would for an Oracle database. Well, not just like it. But, you get the point.
You should have an HS directory in your Oracle home directory. In HS\ADMIN you should find an INITHSODBC.ora file. Copy this to a init<dsnname>. Mine will be INITEnterprisedbDB.ora. In Windows, case is not significant. In general, I lowercase all file names so my file is really initenterprisedb.ora.
Edit this file. You'll need to change the values of two parameters. You must supply the DSN we created above and a log level. For now I am turning logging off. My file looks like this:
HS_FDS_CONNECT_INFO = EnterpriseDB
HS_FDS_TRACE_LEVEL = off
Save the file, making sure to leave it in the HS\ADMIN directory.
Configure The Listener
At the bottom of your listener.ora file (in Oracle home/network/admin) you need to add a new service under the sid_list_listener:
(SID_DESC = (SID_NAME=EnterpriseDB)
(ORACLE_HOME=C:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM=hsodbc)
)
The HSODBC program has under gone name changes. If you are not using 10g, make sure you find the current name to use in the documentation. HSODBC may work in 11g bit I do know the program has been updated and has a new name. The SID_NAME parameter is the ODBC DSN name.
Configure TNSNAMES.ora
You will need to enter a new entry in your TNSNAMES.ora file (in Oracle home/network/admin).
It seems counter intuitive but here you are actually configuring the server where the listener is running not where the remote database is running. Notice the PORT is 1521. That is the port (and the host) where the Oracle listener is listening for connections. ODBC will ensure that your connections make it to the right remote server.
EDB =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)
(HOST=localhost)
(PORT=1521)
)
(CONNECT_DATA = (SERVICE_NAME=EnterpriseDB)
)
(HS = OK)
)
SERVICE_NAME is, again, the ODBC DSN.
Create a Database Link
create database link edb1 connect to "enterprisedb" identified by "edb" using 'edb';
Notice here that I have the name and password quoted. That's because the remote server, EDB AS, is case sensitive. If the user name and password are not quoted, Oracle will send them in upper case and you'll get an error.
I'm calling my link edb1 but you can call it anything that doesn't violate Oracle naming rules.
In the USING section, you need to use the name defined in your tnsnames.ora.
Test the Link
SQL> create database link edb1 connect to "enterprisedb"
identified by "edb" using 'edb';
Database link created.
SQL> select * from dual@edb1;
D
-
X
SQL> select "lanname" from "pg_language"@edb1 where rownum < 5;
lanname
-----------------------------------
internal
c
sql
plpgsql
SQL>
Notice here that, like user name and password, I have the column and table name quoted. Columns and tables in EDB and Postgres are stored in lower case unless quoted during creation.
There are many parameters you can use to tweak HS. For something like an ETL process, you would want to tweak it as much as possible. For an occasional query, plain vanilla is probably good enough.
Take care,
LewisC
