Monday, November 2, 2009

Accessing Data Using Oracle From Non-oracle Databases

There may be some requirements to access the Data residing in different flavors of Databases like MS-SQL, Access, and Sybase from the Oracle database. This can be achieved with the help of creating Heterogeneous Services to connect to non-oracle flavors of the database and also integrate the data residing in them. The HS (Heterogeneous Services) is created with the help of the ODBC drivers for that particular flavor of the database (e.g. For MS-Access we need MS driver for Access). Generic Connectivity is implemented by using a Heterogeneous Services ODBC agent. An ODBC agent is included as part of the Oracle system and is installed in the same ORACLE_HOME and resides in the folder HS.

The following steps needs to be performed in order to configure a Heterogeneous connection in the Oracle Database, those steps are used in big firms as well as Oracle Consulting, Oracle Outsourcing Companies:

1. Preparing the Non-oracle environment from where in the data needs to be integrated into the Oracle database.
2. Creation of the ODBC connection.
3. Test the ODBC drivers to ensure that connectivity is made to the non-Oracle database.
4. Ensure the Global_names parameter in Oracle database is set to False.
5. Configure the Heterogeneous services. This is done with the help of creating an initodbc.ora.
7. Modify the Listener.ora and the TNSNAMES.ORA file so that to connect to the Database.
8. Restart the Listener or START the listener if a new one has been created specific for the new connection.
9. Creation of the Database Link to connect to the HS connection.
10. Test the connection using thee DB Link.

Let us see the above steps in a brief fashion. Here we can consider of having a MS-Access Database from where in the data needs to be accessed into Oracle database:

1. Create the MS-Access database or copy the same to the local server where in the Database is hosted.

2. Create the ODBC connection. This can be done as follows:

Click on START à Control Panel à Administrative Tools and then open Data Sources (ODBC)

Click on the SYSTEM DSN Tab and then click on the ADD button.

Select Microsoft Access Driver (*.mdb) and then click on FINISH.

Also you can find out more at the Software Outsourcing Company website:

After that, enter the Data source name using which you would like to connect to the MS-Access Database (here in our case it’s ChryslerMDB). Also, select the MS-Access file by clicking on SELECT button and then click on OK to complete the configuration.

1. Check for the connectivity and confirm whether the ODBC connection is working fine.

2. Once this is done, Check for the GLOBAL_NAMES parameter in the Oracle database and make sure that it is FALSE. The following query can be used for the same :

SQL> select name,value from v$parameter where name like 'global_names%';


1. Once this is done, we need to create the INIT.ORA file for the HSODBC connection that needs to be created. This needs to be done in %ORACLE_HOME% oracleora92hsadmin folder. (In UNIX, it will be $ ORACLE_HOME oracleora92hsadmin). Create a file saying init.ora file with the following contents : In our case the file should be named as initchryslermdb.ora

HS_FDS_CONNECT_INFO = chryslermdb
In the above chryslermdb id the non-oracle database connection string or the DSN name that we created in Step 2.

1. Once this is done, the next step is to update the Listener.ORA file. We can use the same listener that is been used by the database to access the ODBC connection or a different one can also be configured. Here we will create a new listener for the ODBC connection.

Edit the Listener.ora file in ORACLE_HOMEora92networkadmin folder and add the following entries :
(ORACLE_HOME = C:oracleora92)
(PROGRAM = extproc)

(ORACLE_HOME = C:oracleora92)
(SID_NAME = chryslermdb)
In the above SID_NAME ChryslerMDB is the SID name that we have given for non-oracle database (e.g. MS-ACCESS or MS-SQL). Once this is done start the Listener by executing the following command at the command line .
C:Documents and SettingsimpactadmDesktop>lsnrctl start listener_msaccess
LSNRCTL for 32-bit Windows: Version - Production on 15-AUG-2007 13
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Starting tnslsnr: please wait...
Then the following message will be seen :
Alias listener_msaccess
Version TNSLSNR for 32-bit Windows: Version - Pr
Start Date 15-AUG-2007 13:05:56
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
Listener Parameter File C:oracleora92networkadmin listener.ora
Listener Log File C:oracleora92networkloglistener_msaccess.log
Services Summary...
DEV has 1 service handler(s)
IMPACT02 has 1 service handler(s)
IMPORT_access has 1 service handler(s)
PLSExtProc has 1 service handler(s)
The command completed successfully
C:Documents and SettingsimpactadmDesktop>

Note : If you are using the same listener, then just execute lsnrctl reload command at the command prompt. In case of windows machines, you can just go to SERVICES and restart the listener.
7. The next step is to update the TNSNAMES.ORA file.

Update the TNSNAMES.ORA file with the entries similar to the following :
#ACCESS DB for Chrysler =
(Host = )
(Port = 1522)
(CONNECT_DATA = (service_name = chryslermdb)

1. Once the entire configuration is done, check the connectivity by doing a tnsping command.

2. The next step is to create a Database link to this ODBC connection. This can be done by as follows:

Login to the database and execute the following query :
Here TEST1 is the DB Link name and CHRYSLERMDB.WORLD is the name of the DSN entry in the TNSNAMES.ORA file.
Once this is done, we can do any operations on the Non-oracle database by executing the commands from Oracle database. For e.g.
Note : We have to make sure that the SID name in the TNSNAMES.ORA and LISTENER.ORA and the one in the USING clause of the Create Database Link matches, otherwise we will not be able to connect to the database and will get some errors. Also, the init.ora file at the %ORACLE_HOME% oracleora92hsadmin should be created with the correct SID failing which will lead to errors.


No comments:

Post a Comment