Using MySQL with Oracle Heterogeneous Services

Oracle Database Gateways allow Oracle client applications to access non-Oracle databases. For example, Oracle SQL Developer and MySQL. Oracle Database Gateways and Heterogeneous Services (built into Oracle) present the appearance of a single, local Oracle database, even though the data might be in Oracle, MySQL, SQL Server and so on.

The Oracle Database Gateway for ODBC (DG4ODBC) transparently integrates ODBC databases with Oracle. ODBC provides a uniform, cross-DBMS interface and insulates applications from the database by using middleware known as an ODBC driver to translate the application's requests into something that the database understands. Because it uses an ODBC driver to link Oracle to the target database, DG4ODBC is not tied to a specific database. This flexible architecture enables Oracle to coexist with any database for which an ODBC driver is available.

By using DG4ODBC, you can immediately use your Oracle applications, both from Oracle and third-parties, to access information in an ODBC database as though it was an Oracle database. Applications do not need to be recoded or reconfigured.

DG4ODBC comes as part of Oracle 11g and later, at no additional cost, and can be used with Oracle 10g and later. To integrate ODBC databases with earlier versions of Oracle, refer to our HSODBC tutorial.

Because DG4ODBC can connect Oracle to any database for which an ODBC driver is available, Oracle refer to DG4ODBC as a generic connectivity agent.

You can use an Easysoft ODBC driver with DG4ODBC to connect Oracle to MySQL. The steps in this guide are for MySQL and Oracle on Linux and UNIX.

  1. Check whether your version of DG4ODBC is 32-bit or 64-bit: 
    cd $ORACLE_HOME/bin
file dg4odbc

    If the file command’s output contains "ELF 64-bit LSB executable", or something similar, DG4ODBC is 64-bit, download the 64-bit MySQL ODBC driver for your platform.

    Otherwise, download the 32-bit MySQL ODBC driver for your platform.

  2. Install, license and test the MYSQL ODBC driver on the machine where DG4ODBC is installed.

    For installation instructions, see the MySQL ODBC driver documentation. Refer to the documentation to see which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH or SHLIB_PATH depending on the platform and linker).

  3. Create a DG4ODBC init file. For example: 
    cd $ORACLE_HOME/hs/admin
cp initdg4odbc.ora initmysql.ora
  4. Ensure these parameters and values are present in your init file: 
    HS_FDS_CONNECT_INFO = mysql_odbc_dsn
HS_FDS_SHAREABLE_NAME = /usr/local/easysoft/unixODBC/lib/libodbc.so

    Replace mysql_odbc_dsn with name of a MySQL ODBC driver data source that connects to the target MySQL database.

    If your MySQL server does not require you to supply a user name and a password, include IgnoreAuth=Yes in the HS_FDS_CONNECT_INFO parameter value. For example:

    HS_FDS_CONNECT_INFO = "mysql_odbc_dsn;IgnoreAuth=Yes"
  5. Add this line to increase Oracle / MySQL compatibility: 
    HS_FDS_QUOTE_IDENTIFIER = FALSE
  6. Add an entry to $ORACLE_HOME/network/admin/listener.ora that creates a SID_NAME for DG4ODBC. For example: 
    SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC=
     (SID_NAME=mysql)
     (ORACLE_HOME=oracle_home_directory)
     (PROGRAM=dg4odbc)
     (ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:
            /usr/local/easysoft/lib)
   )
 )

    Replace oracle_home_directory with the value of $ORACLE_HOME. For example, /u01/app/oracle/product/11.2.0/dbhome_1/.

  7. Add a DG4ODBC entry to $ORACLE_HOME/network/admin/tnsnames.ora that specifies the SID_NAME created in the previous step. For example: 
    mysql_connection=
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521))
   (CONNECT_DATA= (SID=mysql))
   (HS=OK)
 )
  8. Start (or restart) the Oracle Listener: 
    cd $ORACLE_HOME/bin
./lsnrctl stop
./lsnrctl start
  9. Connect to your Oracle database in SQL*Plus.
  10. In SQL*Plus, create a database link for the target MySQL database. For example: 
    CREATE PUBLIC DATABASE LINK mysqllink CONNECT TO
"dbuser" IDENTIFIED BY "dbpassword" using 'mysql_connection';

    Replace dbuser and dbpassword with a valid username and password for the target MySQL database.

    – Or, if your MySQL server does not require you to supply a user name and a password:

    CREATE PUBLIC DATABASE LINK mysqllink USING 'mysql';