Connecting to MYSQL databases from Oracle database using DB links

Here is how to do it in Windows – namely Windows Server 2008 64 Bit with Oracle 11.2.0, but should be similar in any other constellation. Apart from the different ODBC setup, it is very similar on any other OS like Linux or Unix an Oracle server is running on.
1. Login to the following and download the odbc connector related to 32-bit or 64-bit.
http://dev.mysql.com/downloads/connector/odbc/
2. Download and install the odbc connector.
3. To install 32 bit in 64 bit windows machines go to  “C:\Windows\SysWOW64\” and run “odbcad32”  as an administrator and configure the ODBC as below.
4. Setup the ODBC data source to your MySQL database
4.1. Go System DSN tab .

4.b. Add the driver

4.c Give the credentials to connect to mysql database and select the database from the list.  Use the “TEST” button to test the ODBC connection .

5. Click OK in the remaining dialog boxes.

“mysql” on the server “localhost″, using the previously created MySQL user “oracle” (the password is also “oracle”).The ODBC data source name is “testmysql1”. If you click the button “Test” you can verify that the connection is working.

6.Configure Listener.ora file

LISTENER2 =

 (DESCRIPTION_LIST =

   (DESCRIPTION =

     (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))

     )

   )

)

SID_LIST_LISTENER2 =

    (SID_LIST=

        (SID_DESC =

             (SID_NAME =mysqltst)

             (ORACLE_HOME = D:\oracle\product\11.2.0\dbhome_1\)

             (PROGRAM = dg4odbc)

        )

    )

It tells the listener to use dg4odbc, and there the SID “mysqltst” – see next step how to configure this SID. ORACLE_HOME of course has to be changed to your ORACLE_HOME directory.

Hint: If you encounter problems with dg4odbc try typing in “dg4odbc” at the command line. If the command is not recognized, add the directory $ORACLE_HOME/BIN (this is where dg4odbc should be located) to your path variable.

7. Configure data source / SID for dg4odbc

Go into the directory $ORACLE_HOME/HS/ADMIN and create a file called “initmysqltst.ora”  (The name convention is init + SID.ora – you can create many SIDs to different ODBC data sources). Write just the following line in the file:

HS_FDS_CONNECT_INFO = mysqltest1

This specifies which ODBC data source to use, in our case “test” which we configured in the first step – this should be enough to establish a connection, of course you can configure a lot of other ODBC parameters in this file but I will not go into details here.

8.  Configure tnsnames.ora

Now, in order to be able to create a database link, we also need to create an entry in $ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ora:

MYSQLLINK =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = mysqltst)

    )

    (HS=OK)

  )

Please note, the HS=OK entry is very important, as well as using the same SID we configured in the two steps before, of course. MYSQLLINK is the name we are going to use for creating the database link in step 10.

9. Restart the Oracle Service and the TNS Listener Service

10. Create database Link

Logon to your Oracle database with DBA rights (e.g. as SYS) and create the database link:

SQL> create public database link mysqltest connect to "oracle" identified by "oracle" using 'mysqllink';

11.  Test database Link and finished

Hint: Note that table names in MySQL are case sensitive so user the double quotes to force the correct case of the table names.

SQL> select * from products@mysqltest;

no rows selected