cancel
Showing results for 
Search instead for 
Did you mean: 

Connecting Oracle to MySql

Marty Metras
Super Advisor

Connecting Oracle to MySql

Any out there who tried to match up data between an Oracle Database and a MySQL database on 2 different servers/Platforms?

I am trying to connect to a MySQL database (Linux) and an Oracle database (HP-UX) at the same time. Something like you can do between Oracles like in the statement:
Select a.field, b.field from Table@DB1 a, Table@DB2 b where a.field = b.field;

What I am doing now is executing a SQL script on the MySQL server that creates a SDF file that I ftp back to the Oracle server then use SQL Loader to put the data into the Oracle database table. Then do the processing on it.

Since I only have to read the data from the MySQL database this seems to be a sloppy way to do this. So I am looking for a Cleaner way to do it.

I went to MySQL's web site and downloaded the MYodbc for HPUX. It is easy to configure but I am missing something. How do I make it connect to the MySQL database? There may be more to this. I loaded the "lib*" binaries and configured them.
Is there a Daemon or a Client I need to run on the Oracle server that lets me use these lib files?

I'm using Oracle8/8i on HPUX 11.0 server and MySQL ver 3.23.44 on a Redhat 6.2 server.

Is there help for me out there?
Marty
The only thing that always remain the same are the changes.
6 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: Connecting Oracle to MySql

Well,

always great fun when you access data from heterogeneous DB source.

May be the Oracle Gateway is an answer (expensive ?).

check these links :
http://www.oracle.com/technology/products/oracle9i/datasheets/gateways/gateways.html

http://www.oracle.com/technology/products/gateways/pdf/Gateways_twp.pdf

Regards,
Jean-Luc
fiat lux
Marty Metras
Super Advisor

Re: Connecting Oracle to MySql

I'll take a look at the Oracle Gateway.
I see this link is for Oracle9i we have no chose. We have to be on Oracle8i for a long while.
Marty
The only thing that always remain the same are the changes.
Jean-Luc Oudart
Honored Contributor

Re: Connecting Oracle to MySql

The other solution might be to use a PC interface on connect through ODBC to DB1 then DB2 ;-) and then run the query locally

or download from mySQL and upload into another table on Oracle.

How big are the tables ? how often do you have to run this query ?

Regards
Jean-Luc
fiat lux
Marty Metras
Super Advisor

Re: Connecting Oracle to MySql

Basicly what I do is get the last 30 days of data I might need on the MySql box(Text file). Then I get it on the the Oracle box and the import it in to an empty table with then process it using SQL to match up records on the 2 servers.
I may only need 10-20 records but I have to sort throught 2000-3000 record to get them.
It works and it does not take much time to process. It is just that I thought there might be an easer way. 2-3 times they need to pull this data. I think it is slow.
If I could nake a drect connect between the two database I just pull the records I need.

On the PC I can ODBC to the MySQL and Oracle and and others all at the same time.
I'm trying to do in a script in UNIX.
Marty
The only thing that always remain the same are the changes.

Re: Connecting Oracle to MySql

Hi Marty:
I did a google and found this wrt SQLServer, may be you can emulate this for MySQL :-

http://www.experts-exchange.com/Databases/Oracle/Q_21032431.html

just scroll down to see the complete process do this. HTH.

How do this on Oracle9i with Heterogeneous services? May be this step-by-step will be useful,

http://www.idevelopment.info/data/Oracle/DBA_tips/Heterogeneous_Services/HETERO_2.shtml

-Dilip
Ralph Grothe
Honored Contributor

Re: Connecting Oracle to MySql

On the latest application CD set of HP-UX you may find (I think on the 4th CD, but look into CD_TABLE_OF_CONTENTS) a prebuild SD depot for MySQL which includes server as well as client components.
Maybe it would suffice to install the client component on your HP-UX Oracle server.
Then you could configure your RH MySQL server to accept connections and selects (or whatever) from your HP-UX box, and use the MySQL client interface (i.e. simply mysql) even in your scripts.
I think this will be easier than the alternative to install an Oracle client on Linux (although this should work as well).
If you are a little into Perl your DBs are even better accessible through the DBI interface, which is a frontend API for all sorts of DBs while the gory details are done under the hood through the e.g. DBD::Oracle and DBD::mysql modules (or whatever DB you try to access).
You simply create a so called database handle which is an object reference to either of your DBs you can connect to.
So you can simultanously hold db handles to a multitude of DBs.
With each db handle you can prepare SQL statements and execute them on the respective DB.
There is even a DBI module called DBD::Proxy which doesn't require client libraries on the box where it is used from.
Instead you communicate in an RPC like fashion to the server that has those libraries.
Madness, thy name is system administration