Export dump data from Oracle to MySQL / PostgreSQL

Question

Export dump data from Oracle to MySQL / PostgreSQL

Navigation

#1 by (0 votes)

0

I'm looking for the right way to export a Oracle g10 database from 16 gigas to MySQL and / or PostgreSQL em>.

I say export because I want to have the new data dump formatted for MySQL / PostgreSQL and then do the migration.

Maintain indexes, coding, procedures, etc.

I think the big problem is always coding from one base to another.

For this, in production we have Windows Server + Oracle g10 + Toad .

mysql oracle postgresql

asked by Sebastian M. 11.03.2017 в 14:18

source

1 answer

Error with fetch_assoc in PHP Capture various data with php file_get_content and preg_match

score 0 · Answer 1

Hi, I want to share my current solution, I still want to automate it.

First I tried with DBConvert as they suggested and it seems to be fine until you come across structures, functions or defaults that are completely incompatible from oracle to mysql

So now I'm doing something more manual but effective from toad :

I select the database select the table and give me the tab " scripts " right there gives me the structure of oracle and with a few small adaptations I convert it to mysql. the same with indexes and foreing keys, etc.

Then I have the option to export the data, here is the same problem as dbConvert, there are data that are inexportables from oracle to mysql and the generated script is very bad.

Of several options I choose

Export format => "sql statements"
destildo todas la casillas , excepto "allow empty files" y "automatic detect shema and name"
option "date format" => "YYYY-mm-dd"
option "time format" => "hh:mm:ss"

this will generate a .sql with the necessary inserts. But here again the problem with the dates, it generates them in a weird format and within a function:

TO_DATE("2017-03-30 00:00:00","2017-03-30 00:00:00 Y COSAS RARAS")

When importing the sql into mysql, this gives an error, because the function TO_DATE that is from oracle does not exist and secondly because I only need to import the date.

To solve simply create a TO-DATE function that receives those 2 parameters and returns only the first one that has the datetime and already.

At the beginning it was enough to replace the text that I did not need but when working with such large tables, .sql were inaccurate.

The function:

DELIMITER //

CREATE FUNCTION TO-DATE(param1 varchar(30),param2 varchar(30)) RETURNS VARCHAR(30)
BEGIN
  RETURN param1 ;
END
//