Oracle: Access MySQL databases from Oracle

In the last post, I created a connection from Oracle to PostgreSQL. Now we'll do the same - but not using PostgreSQL, but MySQL.

Creating a database link to a PostgreSQL database does not work out-of-the-box. But there's a way, which uses UnixODBC for that. Oracle calls it HS ("Heterogenous Services").

Installation

First, we need to install UnixODBC packages - the generic ones, and the ones for the database we want to access:

yum install unixODBC
yum install mysql-connector-odbc

Configuration

Then, we have to configure ODBC.

General database drivers configuration

In /etc/odbcinst.ini, we have to configure each database system - in this case: PostgreSQL:

[MySQL]
Description=Connector/ODBC 9.5 UNICODE Driver DSN
Driver=/usr/local/lib/libmyodbc9w.so
SERVER=localhost
PORT=3306
USER=myuser
Password=
Database=mydb
OPTION=3
SOCKET=

In /etc/odbc.ini we configure access to each database using a unique name:

[mydb]
Description     = mydb
Driver          = MySQL
Server          = <server name>
User            = <username>
Password        = <password>
Database        = mydb
Port            = 3306

Oracle Configuration

Listener

We'll have to configure an Oracle TNS listener which redirects access to the non-Oracle databases. We can either use an existing TNS listener, or we configure a new one. In this case, we use the existing one (LISTENER).

Let's edit $ORACLE_HOME/network/admin/listener.ora:

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC=
   (SID_NAME=pgdb)
   (ORACLE_HOME=/u01/app/oracle/product/19c)
   (PROGRAM=dg4odbc)(ENVS="LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/19c/lib")))

After that, the listener need to be restarted:

# lsnrctl stop
# lsnrctl start

HS Configuration

To allow access to PostgreSQL, we need to fake an Oracle database which then refers to ODBC. To do that, we need to edit $ORACLE_HOME/hs/admin/initpgdb.ora:

HS_FDS_CONNECT_INFO = mydb
HS_FDS_TRACE_LEVEL = ON
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P15
set ODBCINI=/etc

TNSNAMES

As last step, we need TNS entries in $ORACLE_HOME/network/admin/tnsnames.ora:

mydb = 
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=localhost)
      (PORT=1521)
    )
	(CONNECT_DATA=(SID=mydb))
	(HS=OK)
  )

Then, we can create a database link:

SQL> create database link mydb connect to "<username>" identified by "<password>" using 'pgdb'; 

Testing

Unfortunately, we cannot test it like we would do it with an Oracle database, as the DUAL object does not exist:

SQL> select 1 from dual@mydb;

select count(*) from dual@mydb
                     *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ERROR: relation “DUAL" does not exist;
No query has been executed with that handle {42P01,NativeErr = 1}
ORA-02063: preceding 3 lines from PGDB

Also, other views like DBA_TABLES or DBA_VIEWS don't exist in PostgreSQL. So we will need some information about the PostgreSQL structure. Knowing that my MySQL table is actor, I can check it:

SQL> DESCRIBE actor@pgdb

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 actor_id                                  NOT NULL NUMBER(10)
 first_name                                NOT NULL VARCHAR2(540)
 last_name                                 NOT NULL VARCHAR2(540)
 last_update                               NOT NULL DATE

Ok, doesn't look that bad. Let's select some data:

SQL> select first_name,last_name from actor@mydb;

select first_name,last_name from actor@mydb
                  *
ERROR at line 1:
ORA-00904: "LAST_NAME": invalid identifier

Hm, this doesn't work. The reason is, that Oracle objects are automatically created with upper case, unless specified in lower case.

My MySQL object is created on lower case, so I have to use quotes:

SQL> select "first_name","last_name" from "actor"@mydb;

first_name
--------------------------------------------------------------------------------
last_name
--------------------------------------------------------------------------------
Penelope
Guiness

Conclusion

After some configuration, there's a way to access MySQL data from Oracle, which is not too complicated. But keep in mind that

• ODBC is not a high performing solution. If your data is too large, statements will take a long time.
• There will be data conversion between Oracle and MySQL. Before moving to production, check your data, especially for characters like German Umlauts ä, ö, ü and ß. If it does not fit, consider changing the parameter HS_LANGUAGE
• As MariaDB was a fork of MySQL, it still behaves very similar. So creating a connection from Oracle to MariaDB works quite the same - just use the ODBC drivers from MariaDB instead of these provided by MySQL