Access to MySql from Oracle

A. Introduction
According to Oracle note, Oracle Database Gateway for ODBC is intended for low-end data integration solutions requiring the dynamic query capability to connect from an Oracle database to non-Oracle systems. Any data source compatible with the ODBC standards described in this chapter can be accessed using Oracle Database Gateway for ODBC.


Figure 1-1 shows an example of a configuration in which an Oracle and non-Oracle database are on separate machines, communicating through Oracle Database Gateway for ODBC. The client connects to the non-Oracle system through a network.

Figure 1-1 Oracle and Non-Oracle Systems on a Separate Machines

Description of "Figure 1-1 Oracle and Non-Oracle Systems on a Separate Machines"

complete article :
http://docs.oracle.com/cd/B28359_01/gateways.111/e10311/intro.htm#CEGCBAIE.




B. Installation
You need to install an ODBC driver to use Oracle Database Gateway for ODBC.
In my case, i have downloaded  mysql-connector-odbc-5.1.10-1.rhel5.x86_64.rpm from //www.mysql.org
and installed it on my Oracle server (RedHat x86_64), /usr/lib64.




C. Environnement
Oracle host      : Linux Redhat x86_64
Oracle version : 11.2.0.1


Mysql server  : Linux Redhat x86_64
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name                     | Value                     |
+-------------------------+---------------------+
| protocol_version                  | 10                          |
| version                                 | 5.1.47                    |
| version_comment                 | Source distribution  |
| version_compile_machine     | x86_64                  |
| version_compile_os              | redhat-linux-gnu     |
+-------------------------+---------------------+




D. Set up

 1.  /etc/odbc.ini

Using the root user, i have to instantiate the odbc.ini

I'm be using the [mysql] DSN entry.

[mysql]
Driver                         =  /usr/lib64/libmyodbc5.so
UsageCount               = 1
DATABASE               = my_mysql_db 
PORT                         = 3306
SERVER                    = @IP MySql Host
USER                         = mysl_user
PASSWORD              = pwd_mysl_user
CHARSET                 = utf8 ---------------> used while creating tables on  my_mysl_db 
TRACE                      = OFF
TDS_Version             = 8

2. Test the MySql ODBC Driver
You can easily test the MySql driver using the unixODBC tool : isql
[oracle@xxxx etc]$ isql mysql -v

+---------------------------------------+

| Connected!                                |

|                                                  |

| sql-statement                             |

| help [tablename]                        |

| quit                                           |

|                                                 |

+---------------------------------------+




2. Configure  dg4odbc process.


2.1 The listener needs a new entry :

SID_DESC =      
         (SID_NAME=dg4odbc)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
         (PROGRAM=dg4odbc)                     
           (ENVS=LD_LIBRARY_PATH=              
            /u01/app/oracle/product/11.2.0/dbhome_1/   
            :/usr/lib64/:/usr/lib/
            :/u01/app/oracle/product/11.2.0/dbhome_1/lib)
           )     


[oracle@xxxx etc]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-MAR-2012 13:44:22
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521)))
The command completed successfully
[oracle@xxx etc]$

2.2 The tnsnames needs a new entry : 

dg4odbc=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521))
    (CONNECT_DATA=(SID=dg4odbc))
    (HS=OK)
  ) 

The (HS=OK) parameter must be outside the SID section and specifies that this connector 

uses the Oracle Heterogeneous Service Option.

[oracle@SRVD-SSM etc]$ tnsping dg4odbc
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-MAR-2012 13:45:04
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521)) (CONNECT_DATA=(SID=dg4odbc)) (HS=OK))
OK (0 msec).


2.3 init.ora

The SID is also relevant for the init.ora file of the gateway. The name of the file is init.ora.  In this example it is called  initdg4odbc.ora.

The file is located at $ORACLE_HOME/hs/admin.

/u01/app/oracle/product/11.2.0/dbhome_1/hs/admin/initdg4odbc.ora :
HS_FDS_CONNECT_INFO=mysql ----------> odbc.ini    [mysql]
 HS_FDS_TRACE_LEVEL=0
HS_FDS_SHAREABLE_NAME=/usr/lib64/libmyodbc5.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
HS_FDS_SQLLEN_INTERPRETATION=64
set ODBCINI=/etc/odbc.ini
set ODBCSYSINI=/etc

2.4 Configuring the environment:
I create a database link from my oracle user to the mysql user :



create database link linkmysql
  connect to "mysl_user"         -------------------> odbc.ini    USER = mysl_user
  identified by "pwd_mysl_user" -------------------> odbc.ini    PASSWORD  = pwd_mysl_user
  using 'dg4odbc';

Username and password must be in double quotes !!


2.5 Test
in the mysql database, i create the table my_sql_table (id number, dest char(5)), and i insert the following data :  
id             dest
----          ----
1              dest1
2              dest2

 from my oracle user, i execute : select * from  " my_sql_table"@ linkmysql;

SQL> select * from " my_sql_table"@linkmysql;
ERROR:
ORA-28528: Heterogeneous Services datatype conversion error
ORA-02063: preceding line from LINKMYSQL
no rows selected
SQL>


To avoid the ORA-28528 error add to the initdg4odbc.ora :  HS_FDS_FETCh_ROWS=1
Metalink note [ID 1080048.1].


connect with a new oracle session :

SQL> select * from " my_sql_table"@linkmysql;

id             dest
----          ----
1              dest1
2              dest2  

SQL>