If don't own proprietary database engine, what is best way to convert database to mysql?

Question

I work for a very small company. I was recently faced with the question of whether there is a good way to convert a proprietary database to a MySQL database without owning the proprietary database engine e.g. if one is given a large oracle database file (or choose your favorite proprietary database engine format), but doesn't have a license for the oracle database engine, is there a good, perfectly reliable way to convert it to a MySQL database format that can be read with the MySQL database engine? My question is very vague as to which proprietary format is the source just because there would be multiple sources and it looks like they would be "various and sundry". My suspicion is that there is no perfectly reliable way, especially for a wide variety of proprietary databases. If there are a few proprietary formats for which this is possible, I would still be interested in knowing, though "various and sundry" is probably the real issue. Minimizing cost, effort and correct conversion are key so I think this probably is in the not possible list.

-John

Answer 1

Most commercial DBs have a trial or limited download version, should at least be enough to export the data and schema.

However you do it, it's probably safer to read the schema and create the structure in MySQL then export each table as say CSV and re-import it into MySQL rather than rely on a direct conversion tool.

ps. Of course if you have a lot of stored procedures or custom Oracle specific SQL then it's going to hurt a lot more!

Answer 2

Most databases (and vendors) support some sort of SQL DDL/DML export capability. They may not advertise it loudly, but it's there. MySQL and PostgresSQL both have this sort of capability. Microsoft has the SQL Server Database Publishing Wizard. Oracle has this capability as well. You might be able to convince your data source to export the data, instead of merely dumping it.

All of these tools have limitations, particularly when it comes to BLOBs and similar data types. Exporting the data typically takes longer than merely dumping it, and the resulting files may be significantly larger. The advantage is that the resulting SQL scripts are amenable to being edited/converted/tweaked to match your target database's prefer SQL syntax.

Answer 3

For Oracle, you can look into Personal Edition (only available for Windows). With a short-term license (eg one year) it is a low-cost [from about $100] way for an individual to get the functionality to process large Oracle databases with the full feature set.

It is licensed on a single-user basis, and the definition of a user is quite wide. As such, you are not allowed, for example, to distribute reports from a Personal Edition database to a bunch of people (each would be counted as a user). A one-off migration from Oracle to another platform should be okay. A repeated, regular extraction would be more likely to be seen as being part of a process of supplying information to multiple users.