Connecting to Oracle Databases

From time to time, many of us are confronted with Oracle databases. Whether it's for capturing the database schema, to exchange data, to write a database application, etc.

I always found that connecting to an Oracle database is more diffucult than connecting with whatever other type of database.

This is mainly caused by the fact that you need to install the Oracle client software (aka SQL * NET client software) and configure it. You have to define the databases you want to connect to and because Oracle has its very specific terminology which on top of this changes from time to time it's much harder than it should be.

Oracle uses the TNSNAMES.ORA file to store the configuration data. This file is defined in an OOPS format, but there's also a configuration tool which is supposed to make life easier.

For those of you that don't want to know the inner details of Oracle, I want to share these tips.

Connecting to an Oracle 10g database using an Oracle 9 client

- To connect to an Oracle 10g database using an Oracle 9 client, you need to switch on the 'Use Oracle8 Release 8.0 Compatible identification' in the Oracle Net manager tool. Due to the fact that you are using an Oracle 9 client, this intuitively does not seem to be the way to do it.

Easy-connect

- As of Oracle 10g, a feature has been foreseen with the name 'Easy-connect'. 'Easy connect' and 'Oracle' in a positive sentence ? Indeed, as of Oracle 11, connecting to Oracle is as simple as connecting to SQL Server, DB/2 or whatever other type of database.
First of all, the - sometimes - problematic step of having to use the Oracle NET Manager is no longer required.
All you have to do is use the following syntax : 'sqlplus username/password@[//url] [:portnumber][/service Name]'.

Note that the two forward slashes are doing the magic here.

Some examples :
- sqlplus mario/password@//www.developerdotstar.com/MYSERVICE connects to the Oracle database residing on the - imaginary - www.developerdotstar.com server (the service MYSERVICE is listening on the default port 1521).

- sqlplus mario/password@//strike where 'strike' is the hostname of the PC on which the Oracle database is residing (the database service name is equal to the host name, which means that you don't need to specify it). Again, the database service is listening on port 1521.

- sqlplus mario/password@//strike:1700/DB1 where 'strike' is the hostname of the PC on which the Oracle database service DB1 is residing, listening on port 1700.

Some best-practices :

In the construction phase: when you are writing an application that needs to connect to an Oracle 10(+) database, you can use easy-connect in the early stages of development so save you from the burdon of having to do SQL*NET configuration work.

However, by the time you are about to release the first iteration of your application it should become configurable. You can do this easily by storing your connection string (preferably w/o user name and password) in a configuration file or in your configuration repository.

In the transition phase, it becomes important to think about how you'll connect to the database. Typically, the preference would be to use an alias (= logical name) instead of the name of a machine (physical name) and also to modify the default service name. For security reasons it's adviced not to use the standard Oracle port number 1521.

About the author

Mario Van Damme is a software architect, working for quite a number of years in the medical industry, and prior to that in the insurance and banking industry.
He can be contacted by e-mail at: mvandamme~AT~sopragroup.com and mario.vandamme.mv~AT~belgacom.net.