Dear friends,

I want to convert oracle 9i database to mysql using JSP technology. Does there anyone who have a hint or some web links about this task? I searched the internet and found all of them are not free and no source code. In fact, what I want to do is really simple, I just want to first, reading the oracle database table structure information and then create the exactly same table in mysql, and then copying all the data in oracle database table to mysql table. But I don¡¯t know how to retrieve the oracle table structure using JDBC. And also I want to retrieve all the tables in the oracle database, so I can choose from them to convert to mysql. It seems that the structure in oracle usually have the tablespace information, I don¡¯t know if I can create database in mysql using this tablespace information. And I don¡¯t know where I can find the information about the functions in JDBC? For example, how to retrieve the table structure? Once I retrieve the table structure and create the exactly same table structure in mysql, then I can fetch all the data in oracle table to mysql table.

Any hint will be great appreciated!

Merry Christmas and Happy new year!
Sammy

Hi, one way to do it is to query the object's structure trough SQL using JDBC, then you will have all the information you need available in Recordsets (datatypes, names...)

In Oracle in order to get all tables (or other types of objects) you can:

SELECT Object_Name,Object_ID FROM user_objects WHERE Object_Type='TABLE'

Then you can iterate trough the resulting Recordset and for each table you have gotten on the previous query. get the column structure like this:


SELECT * From user_tab_columns WHERE Table_Name='TableName'


This way you can conditionaly build up Create table statements with proper datatype mappings...


The same method could be used for any type of objects...
Doing the same from Oracle to SQL Server ... a pain in the arse....

Good luck... Lluis.