Step by step instructions for installing and configuring Oracle’s Heterogeneous Services (HSODBC) to connect from an Oracle instance to MySQL (source database) running on a remote Linux machine.

A) INSTALLING THE GATEWAY AND THE ODBC DRIVER FOR THE NON-ORACLE SYSTEM ON ORACLE SERVER

1- Verify installed packages.
2- Install ODBC driver for MySQL.
3- Configure odbc.ini.
4- Configure odbcinst.ini.
5- Verify connectivity with isql.
The entire install process is done as root.

1- Verify installed packages:

[root@ofsvorcl ~]$ rpm -qa | grep ODBC
unixODBC-2.2.11-7.1
unixODBC-2.2.11-7.1
unixODBC-devel-2.2.11-7.1
unixODBC-devel-2.2.11-7.1

2- If not found install myODBC and unixODBC as a dependency:

yum install mysql-connector-odbc 

3- Once this is ready open the ‘odbcinst.ini’ (this file lets the user specify as many different drivers as he wants). Comment out the Postgre SQL stuff with ‘#’ and uncomment the MySQL example:

[root@ofsvorcl etc]# pwd
/etc
[root@ofsvorcl etc]# cat odbcinst.ini
# Example driver definitinions

# Included in the unixODBC package
[PostgreSQL]
# Description = ODBC for PostgreSQL
# Driver = /usr/lib/libodbcpsql.so
# Setup = /usr/lib/libodbcpsqlS.so
# FileUsage = 1

# Driver from the MyODBC package
# Setup from the unixODBC package
#[MySQL]
#Description = ODBC for MySQL
#Driver = /usr/lib/libmyodbc.so
#Setup = /usr/lib/libodbcmyS.so
#FileUsage = 1

[MySQL]
Description = MySQL Driver ODBC
Driver = /usr/lib64/libmyodbc3.so
Setup = /usr/lib64/libodbcmyS.so
CPTimeout =
CPReuse =
FileUsage = 1

[ODBC Drivers]
MySQL = Installed

4- Next, we need to configure the ‘/etc/odbc.ini’ (this file contains parameters for ODBC connections – such as, user name, password, database and host. Each application that requires a odbc connection can have it’s own entry or share one):

[root@ofsvorcl etc]# cat odbc.ini
[svilux_orto2003]
Description     = Orto2003 su Svilux
Driver          = /usr/lib64/libmyodbc3.so
Server          = 
Database        = 
LogonID         = 
Password        = 
Port            =
Socket          =
Option          = 3145731
Stmt            =

[ODBC Data Sources]
svilux_orto2003 = MySQL

5- Utilize the ‘isql’ command to connect to our MySQL database. Isql will call on the specified profile stated in the ‘/etc/odbc.ini’ file:

 [root@ofsvorcl Desktop]# isql svilux_orto2003 <user> <psw> -v
[MYODBCUtilReadDataSource.c][233][ERROR] Unknown attribute (LogonID).
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from settori;
+-------+-------------------------------+-------+
| CODSET| DESSET | CODREP|
+-------+-------------------------------+-------+
| 50 | FRUTTA e VERDURA | 3 |
| 34 | FIORONI | 4 |
+-------+-------------------------------+-------+
SQLRowCount returns 2
2 rows fetched
SQL>

B) CONFIGURING ORACLE DATABASE GATEWAY (DG4ODBC) FOR ODBC

1- Configure the Gateway (DG4ODBC) Initialization Parameter File.
2- Configure Oracle Net for the Gateway.
3- Configure the Oracle Database for Gateway Access.
4- Create Database Links.
5- Verify.

1- Create an initialization file (initsvilux_orto2003.ora) for your Oracle Database Gateway for ODBC (Oracle supplies a sample initialization file, $ORACLE_HOME/hs/admin/initdg4odbc.ora) and rename it to init<sid>.ora, where is the system identifier (SID) you want to use for the instance of the non-Oracle system to which the gateway connects. The gateway system identifier (SID) is an alphanumeric character string that identifies a gateway instance. You need one gateway instance, and therefore one gateway SID, for each ODBC source you are accessing.

[oracle@sigodbprd admin]$ pwd
/oracle/product/11.2.0/dbhome_2/hs/admin
[oracle@sigodbprd admin]$ cat initsvilux_orto2003.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
# HS init parameters

HS_FDS_CONNECT_INFO = svilux_orto2003
# HS_FDS_TRACE_LEVEL = 
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_FETCH_ROWS = 1
# ODBC specific environment variables
set ODBCINI=/etc/odbc.ini

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

2- The gateway (DG4ODBC) requires Oracle Net to communicate with the Oracle database. After configuring the gateway, perform the following tasks to configure Oracle Net to work with the gateway. Configure Oracle Net Listener for the Gateway (listener.ora):

[oracle@ofsvorcl admin]$ pwd
/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@ofsvorcl admin]$ cat listener.ora
# listener.ora Network Configuration File:
# /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ofsvorcl.finiper.it)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /oracle/app/oracle

LISTENER_ODBC =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
)

SID_LIST_LISTENER_ODBC=
(SID_LIST=
(SID_DESC=
(SID_NAME=svilux_orto2003)
(ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM=dg4odbc)
)
)

and stop and Start the Oracle Net Listener for the Gateway:

$ lsnrctl stop$ lsnrctl start

Check the status of the listener with the new settings, as follows:

$ lsnrctl status

3- Before you use the gateway to access an ODBC data source you must configure the Oracle database to enable communication with the gateway over Oracle Net (tnsnames.ora):

[oracle@ofsvorcl admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:
# /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

FNSIGO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ofsvorcl.finiper.it)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fnsigo.finiper.it)
)
)

ORTO_2003 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ofsvorcl.finiper.it)(PORT = 1522))
(CONNECT_DATA =
(SID = svilux_orto2003))
(HS=OK)
)

4- Any Oracle client connected to the Oracle database can access an ODBC data source through the gateway. The Oracle client and the Oracle database can reside on different machines. The gateway accepts connections only from the Oracle database. Create Database Links (Oracle instance) to source database (MySql):

CREATE PUBLIC DATABASE LINK 
  CONNECT TO 
  IDENTIFIED BY 
  USING ;

5- Accessing ODBC Data (MySql) from Oracle (Oracle instance):

select pv.*,s.* from "puntivendita"@ORTO_2003 pv
inner join "societa"@ORTO_2003 s on s.CDS=pv.SOCAPP
order by CODPV

Most valuable links
http://www.savelono.com/linux/how-to-use-odbc-driver-to-connect-to-mysql-database-in-fedora-10.html
http://download.oracle.com/docs/cd/B28359_01/gateways.111/b31042/configodbc.htm#CIHEGGDC