Connecting Oracle to MySql

[Marty Metras]

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

[Jean-Luc Oudart]

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

[Marty Metras]

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

[Jean-Luc Oudart]

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

[Marty Metras]

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

[Dilip Kumar_4]

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]

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.