Making a connection from Oracle XE to MySQL with ODBC

After searching the Web I found a nice description from James Koopmann: Making a Connection from Oracle to SQL Server

Then I found a Metalink note: WIN NT - Generic Connectivity using ODBC [ID 114820.1]

What software do you need? MySQL ODBC driver

Now follow these steps:
1. Add a new file:
<ORACLE HOME>\hs\admin\initMYSQL_DB.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MYSQL_DB
HS_FDS_TRACE_LEVEL = OFF

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

2. Update your Listener.ora (In my case I use the existing one):
...
(SID_DESC=
(SID_NAME=MYSQL_DB)
(ORACLE_HOME=F:\oracle\app\oracle\product\10.2.0\server)
(PROGRAM=hsodbc)
)
...

3. Update your TNSNAMES.ora and restart your Listener:
...
MYSQL_DB =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.1)(PORT=1521))
(CONNECT_DATA=(SID=MYSQL_DB))
(HS=OK)
)
...

4. Create database link inside your database:
create database link MYSQL_DB
connect to "test_db" identified by "test_db"
using 'MYSQL_DB';

5. SQL> select * from test_table@MYSQL_DB;

Issues I know about: Invalid identifier problem

Post Tags:

MySQL OracleXE