Oracle Connections to Non-Oracle Databases

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

Technorati : database, dblink, gateway, heterogenous, links, oracle

My Book Has Been Printed

Well, it's taken over a year but it has finally arrived. I started writing the book back in Aug 2006. I finished in late Jan 2007 and the technical editor finished his work in March. Now, In Jan 2008, EnterpriseDB: The Definitive Reference is available. I'm glad I didn't wait for the movie. ;-)

It's kind of ironic. Just this morning, I posted that I am working on my second book. My wife called me at work and told me two boxes of books had arrived. 2008 is turning out to be a good year. I'll have some additional news in the near future.

Anyway, the book looks good. I've already found a few typos and grammar errors just skimming around. I like the font and the pages look really crisp. All in all, I have to say I am very happy that the book is out.

Check out some photos:





I think I want to give a couple away. Maybe some kind of contest for the blog readers. Any ideas of something fair? I am willing to pay shipping for a couple but that would only be here in the US. Overseas shipping gets expensive.

LewisC

oracle enterprisedb

Hotsos Profiler Extension for SQL Developer

Hotsos has released its Profiler extension for SQL Developer. The extension is free but you must have a license for the profiler for the extension to do you any good.

The profiler works almost like the Explain Plan tool in SQL Developer. Enter the text you want to profile, press a key and then view the output.

You do need access to the trace files that are generated. The page linked above had instructions on how to enable access and what permissions you need.

A neat feature is that you can save profiling sessions for historical access. That can be a life saver if you are trying to tune and are trying out different approaches. Instead of keeping your own backups, use the history. You can also share that history so that all of you developers have the same baseline. Nice.

Hotsos also provides a discussion group for questions relating to the extension. If you are a Hotsos profiler user, you should check out this extension.

oracle hotsos

CUNY Chooses Oracle

City University of New York (CUNY) has standardized on PeopleSoft Financials, Human Capital and Campus Solutions. Of course, all of this will be running on Oracle 11g and, eventually, Fusion.

"We chose Oracle to provide the highest quality service and advanced solutions to accommodate our institutions' various sizes and non-traditional programs," said Brian Cohen, CUNY's chief information officer and co-project leader for CUNY FIRST. "We chose Oracle's PeopleSoft Enterprise Campus Solutions 9.0 for the new capabilities this version offered and look forward to leveraging it to transform our student information system."

CUNY selected Oracle due to the functionality and scalability of its applications - vital factors considering CUNY's unique composition of senior and community colleges and other programs and institutions - and the number of PeopleSoft implementations within higher education institutions in the U.S. Oracle's application solutions and database will enable CUNY to implement a single database instance for the entire university in a true multi-institution configuration.

"We chose Oracle to provide the highest quality service and advanced solutions to accommodate our institutions' various sizes and non-traditional programs," said Brian Cohen, CUNY's chief information officer and co-project leader for CUNY FIRST. "We chose Oracle's PeopleSoft Enterprise Campus Solutions 9.0 for the new capabilities this version offered and look forward to leveraging it to transform our student information system."

CUNY selected Oracle due to the functionality and scalability of its applications - vital factors considering CUNY's unique composition of senior and community colleges and other programs and institutions - and the number of PeopleSoft implementations within higher education institutions in the U.S. Oracle's application solutions and database will enable CUNY to implement a single database instance for the entire university in a true multi-institution configuration.

Ron Spalter, deputy chief operating officer and co-project Leader for CUNY FIRST stated, "It was significant to us that Oracle offered CUNY the Oracle Insight program, which we refined to focus on a high profile area for CUNY students - financial aid. We are working with Oracle to frame, analyze and resolve a number of outstanding opportunities in this area."

The City University of New York is the nation's largest urban public university. CUNY comprises 23 institutions: 11 senior colleges, six community colleges, the William E. Macaulay Honors College at CUNY, the Graduate School and University Center, the CUNY Graduate School of Journalism, the CUNY School of Law at Queens College, the CUNY School of Professional Studies and the Sophie Davis School of Biomedical Education. The University serves more than 231,000 degree-credit students and 230,000 adult, continuing, and professional education students. College Now, the University's academic enrichment program for 32,500 high school students, is offered at CUNY campuses and more than 300 high schools throughout the five boroughs of the City of New York.

oracle peoplesoft