oracle to MySQL for a novice

A client has an application created in Java for an oracle database. He needs it converted to a mySQL database.

I have only worked with PHP/MySQL applications. Any pointers here on things to look for or how to proceed?

Many thanks,
Patty

I have work with PHP-Oracle once. And it's not the very complicated one. So, I'm not the top expert. But I'm open for discussion.

Converting from Oracle to MySQL??? The simplest and sincerest advice I can give you is "DON'T". You don't know what you are getting yourself into.

MySQL has so many more limitations than Oracle that you will probably have some real problems getting the same logic to work in MySQL. This is especially important if you have no experience with databases beyond MySQL.

Now, once we get beyond this, yes, it is possible to put data from an Oracle database into MySQL. Depending on the features used in the Oracle database, though, the difficulty level ranges from somewhat troublesome to nearly impossible.

If the Oracle database has any views, triggers, or stored procedures, you will have to live without them in MySQL. The best you can hope for is to use InnoDB tables so you at least have foreign key integrity. Also, there is the question of datatypes. MySQL's range of available datatypes for columns is quite limited, so you might have to do some research there.

In all honesty, I would say you need to post some information about the Oracle database's structure to get any further advice.

IF the customer really wants a (free) open source database system to handle this work, PostgreSQL , SAP DB or Firebird will be much better choices. I recommend PostgreSQL first, because it is probably the most mature open source DBMS available, and its syntax and feature set are very similar to Oracle. Again, however, if you have no knowlege of DB systems beyond MySQL, I think you are going to need some serious study to be able to understand the abilities of enterprise-level SQL systems. Be prepared to do your homework .

This is worth repeating. There is a lot more to DB design than what MySql offers. If this application was originally designed for Oracle, you can almost guarantee it's way more complex than stuff you've done in Mysql.

I agree with rycamor that it would be much genter on your sanity if you used another RDMS such as Mysql. Actually if you remove some of the things such as triggers you may very well break the application because the application relies totally on the database. You could even find yourself recoding a lot of things in the app to make up for those losses.

I have to agree with the other folks. I haven't used Oracle since 8i, but if you have it either stick with it or move to another enterprise system such as DB2. I can understand wanting to move from oracle. Our former CTO circa 2000 - 2001 read all the reviews and flashy ads in magaizes and failed to heed our advice and we had nothing but problems with Oracle.

MySQL has its place, and it is not a stand in for Oracle. If you are going to port, you might as well start from the ground up. You'll proably spend less time.

Just wanted to add that converting from just about ANY database to any other database, is a royal pain in the neck. Unfortunately, SQL stands for "Structured Query Language", not "Standard Query Language" and every vendor has their own dialect. Also, there are a bunch of functions that may be available on one engine, but not on another --- e.g., each engine has different string manipulation functions. After you add stored procs, triggers, constraints, user defined types etc. in the mix, porting becomes a really involved process.

I was involved in a project which was originally written to work with SQLBase (now Centura). Getting the code to also work on Oracle was relatively easy, since SQLBase was founded by an ex-Oracle person and used similar syntax, and also the project only used a few stored procedures. Dunno if the reverse is that easy though. BTW, the higherups decided to make a version of the product, to work with SQL Server. That's when things got rather hairy in a hurry