aspose file tools*
The moose likes JDBC and the fly likes [Best practice] From Oracle to MySQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "[Best practice] From Oracle to MySQL" Watch "[Best practice] From Oracle to MySQL" New topic
Author

[Best practice] From Oracle to MySQL

Ritchie Warsi
Greenhorn

Joined: Nov 02, 2004
Posts: 29
Hey there,
We are building a Struts webapp for a client that will use a MySQL database.
The webapp will only do getMethods (selects).
The main database were all the info is, is an Oracle DB and so somehow the necessary data will have to go from the Oracle to the MySQL DB.
As the budget is rather limited, I would like to ask you guys with experience what the best way is to do this?
We were thinking of the following:
- Some kind of scheduling, being done via the commandline
- Do a dump of the oracle db to csv or xml and upload it to MySQL
- Some kind of transfer application that will do this locally (as Oracle is NOT connected to the internet and MySQL is)

I hope you understand above points.
Does anyone of you have experience with this? Commercial applications will be our last thing to do, so please advise me other methods.
I was thinking something like, since Tomcat can handle multiple datasources, even the struts webapp can handle multiple datasources, would it be possible to define 2 datasources for the app or Tomcat and somehow do a transfer from time to time?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Just curious - why use two databases?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30960
    
158

Ritchie,
I think we need to investigate the requirements a bit more before recommending an approach.

How often do you need to transfer the data? Is it done on a schedule or on demand? How large is the data? Does it change a lot between each transfer or just a little?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Ritchie Warsi
Greenhorn

Joined: Nov 02, 2004
Posts: 29
The use of 2 databases is not an issue we, as programmers of the webapp, can give advise on. It's something they want to keep like it is.
They've used the oracle database for local purposes, inserting data in it from clients and such and other company related data that is only used locally.
The MySQL DB came along with the implementation of the webapp we have to build and is only necessary to have SOME (not all) info from the Oracle DB, for access with the webapp (only selects mostly).

The data needs to be transfered every day, on schedule or demand is not certain yet. We're talking about 8 tables here, not really large data, but not small either.
The data changes every day, that's why the transfer needs to be on a daily basis.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

A scheduled export is probably the easiest route. You could create some scripts to export the data you need and import it into MySQL and run them as a cron job/scheduled task. Oracle exp and mysqlimport could do it.


However here's some (unasked for, I know) advice. If I was working on this, I'd ask the DBA to explain why you need to export a view of the database to another schema, rather than just setting up the correct user privilages. And he'd need to come up with a pretty convincing reason, since the requirement as it stands creates the need for a lot more development and ensures the schema your web app sees will almost never be uptodate.
Ritchie Warsi
Greenhorn

Joined: Nov 02, 2004
Posts: 29
Yeah I know it's pretty strange. They said something about the Oracle DB not being connected to the internet by any means, because of other data in it. User priviliges would indeed be the right way, but hell, I'll have to ask again what good reasons they have.

Tnx for your advice and the advice on the approach to take. We'll look into it immediately.
Other advice is always still welcome.

edit: Oracle exp makes a dump right? As far as I know MySQL can't read/import Oracle dumps...or has this changed recently?

edit2: Nevermind, Oracle's CSV export can be read by MySQL it seems.
[ January 06, 2005: Message edited by: Ritchie Warsi ]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30960
    
158

Ritchie,
I agree with Paul's recommendation. I've seen some systems where the "main" database is unavailable for security reasons. Other reasons we've used two databases are for presentation and efficiency.
Charlie Browninge
Greenhorn

Joined: Jan 17, 2005
Posts: 1
Originally posted by Ritchie Warsi:

Other advice is always still welcome.

edit: Oracle exp makes a dump right? As far as I know MySQL can't read/import Oracle dumps...or has this changed recently?

edit2: Nevermind, Oracle's CSV export can be read by MySQL it seems.

[ January 06, 2005: Message edited by: Ritchie Warsi ]



Hi Ritchie,

I don't know how tight is your budget for this project.
You may want to look FastReader www.wisdomforce.com

FastReader extracts large Oracle tables into flat file with any delimiter (csv type)or xml. At same time FastReader generates loaders for major databases, including MySQL. You can easily set up some automatic scripts with that. I am using FastReader to unload very large Oracle tables (tens millions of rows) into MS SQL Server. Works very well for me, fast and effective, supports most Oracle data types, including binary, blobs, cblobs, etc.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: [Best practice] From Oracle to MySQL