Re: export db to oracle
Hi, Actually all the helpful tips that I have gotten have caused me to review the requirements! I now realise that that csv or xml files for the storage of an extract would be helpful, for testing and validation. a mysqldump might do that job too, but the output from mysqldump --compatible was rejected by oracle. -Syd - Original Message From: Kevin (Gmail) kfoneil...@gmail.com To: Johan De Meersman vegiv...@tuxera.be; Shawn Green (MySQL) shawn.l.gr...@oracle.com Cc: Sydney Puente sydneypue...@yahoo.com; mysql@lists.mysql.com Sent: Wed, 17 November, 2010 18:17:38 Subject: Re: export db to oracle Hello, It should be possible to connect Oracle to the MySQL (or other) database using a DBlink (using a MySQL ODBC driver) the tables could then be copied using PLSQL. Maybe you could link directly to Oracle and copy the code using MySQL procedures or scripts (I have more experienc of Oracle which works quite well as I described) This way, you can avoid use of external files and CSV etc. It is very likely quicker since you can use bulk loads or 'select into' routines once you have the right table structures and field type in place. This is a technique that I have used for ETL and data integration and it is very manageable. You can trap errors using cursors if the data has anomalies. Kevin O'Neill - Original Message - From: Johan De Meersman vegiv...@tuxera.be To: Shawn Green (MySQL) shawn.l.gr...@oracle.com Cc: Sydney Puente sydneypue...@yahoo.com; mysql@lists.mysql.com Sent: Wednesday, November 17, 2010 8:58 AM Subject: Re: export db to oracle On Wed, Nov 17, 2010 at 1:43 AM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 11/16/2010 15:14, Sydney Puente wrote: Hello, How can I export a mysql 5.0.45 db to Oracle? mysql is going to stau but I need to pass the data to oracle, just so the data can be transfered. I have carried out a mysql dump. This seems fine.create table etc. about 20 MB in total. Any ideas? It is on Redhat if that makes a difference. I suggest you also look at the syntax for SELECT INTO OUTFILE, too. Dumps are usually scripts of SQL statements that Oracle may not read appropriately. I'm not quite sure which formats Oracle reads in, although CSV is probably a good guess. if you disable mysqldump's extended insert syntax, however, I think the actual insert statements should be perfectly fine for most any database. You may need to tweak create statements for datatypes and syntax, though; it may be easier to just recreate the emtpy tables by hand. I think I also have vague memories of an option to use ANSI-SQL standard syntax, although that might just as well have been some third-party tool. And, speaking of third-party tools: tOra can (if well-compiled) be used to manage both MySQL and Oracle; maybe that nice tool can help you. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: export db to oracle
On Thu, Nov 18, 2010 at 2:54 PM, Sydney Puente sydneypue...@yahoo.com wrot a mysqldump might do that job too, but the output from mysqldump --compatible was rejected by oracle. Hmm. Interesting, you might want to file an issue about that - now that MySQL is oracle-owned, you'd expect at least that to work, wouldn't you :-p -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: export db to oracle
On Wed, Nov 17, 2010 at 1:43 AM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 11/16/2010 15:14, Sydney Puente wrote: Hello, How can I export a mysql 5.0.45 db to Oracle? mysql is going to stau but I need to pass the data to oracle, just so the data can be transfered. I have carried out a mysql dump. This seems fine.create table etc. about 20 MB in total. Any ideas? It is on Redhat if that makes a difference. I suggest you also look at the syntax for SELECT INTO OUTFILE, too. Dumps are usually scripts of SQL statements that Oracle may not read appropriately. I'm not quite sure which formats Oracle reads in, although CSV is probably a good guess. if you disable mysqldump's extended insert syntax, however, I think the actual insert statements should be perfectly fine for most any database. You may need to tweak create statements for datatypes and syntax, though; it may be easier to just recreate the emtpy tables by hand. I think I also have vague memories of an option to use ANSI-SQL standard syntax, although that might just as well have been some third-party tool. And, speaking of third-party tools: tOra can (if well-compiled) be used to manage both MySQL and Oracle; maybe that nice tool can help you. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: export db to oracle
On Wed, Nov 17, 2010 at 2:26 PM, who.cat win@gmail.com wrote: Maybe you can dump as a csv format,then create table all tables in oracle .After that you can write a script program format the csv to oracle which can be recognized. MySQL's select into outfile may well be good enough to manage to output oracle-formatted inserts; and as I said standard (non-extended) insert syntax is probably good enough for oracle anyway. If you're going to be programming anyway, why not just write something that connects to both DBs and inserts into Oracle using prepared statements for speed ? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: export db to oracle
Hello, It should be possible to connect Oracle to the MySQL (or other) database using a DBlink (using a MySQL ODBC driver) the tables could then be copied using PLSQL. Maybe you could link directly to Oracle and copy the code using MySQL procedures or scripts (I have more experienc of Oracle which works quite well as I described) This way, you can avoid use of external files and CSV etc. It is very likely quicker since you can use bulk loads or 'select into' routines once you have the right table structures and field type in place. This is a technique that I have used for ETL and data integration and it is very manageable. You can trap errors using cursors if the data has anomalies. Kevin O'Neill - Original Message - From: Johan De Meersman vegiv...@tuxera.be To: Shawn Green (MySQL) shawn.l.gr...@oracle.com Cc: Sydney Puente sydneypue...@yahoo.com; mysql@lists.mysql.com Sent: Wednesday, November 17, 2010 8:58 AM Subject: Re: export db to oracle On Wed, Nov 17, 2010 at 1:43 AM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 11/16/2010 15:14, Sydney Puente wrote: Hello, How can I export a mysql 5.0.45 db to Oracle? mysql is going to stau but I need to pass the data to oracle, just so the data can be transfered. I have carried out a mysql dump. This seems fine.create table etc. about 20 MB in total. Any ideas? It is on Redhat if that makes a difference. I suggest you also look at the syntax for SELECT INTO OUTFILE, too. Dumps are usually scripts of SQL statements that Oracle may not read appropriately. I'm not quite sure which formats Oracle reads in, although CSV is probably a good guess. if you disable mysqldump's extended insert syntax, however, I think the actual insert statements should be perfectly fine for most any database. You may need to tweak create statements for datatypes and syntax, though; it may be easier to just recreate the emtpy tables by hand. I think I also have vague memories of an option to use ANSI-SQL standard syntax, although that might just as well have been some third-party tool. And, speaking of third-party tools: tOra can (if well-compiled) be used to manage both MySQL and Oracle; maybe that nice tool can help you. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: export db to oracle
You can try using Oracle SQL Developer Tool which got the data import function from MySql. On 11/17/2010 4:14 AM, Sydney Puente wrote: Hello, How can I export a mysql 5.0.45 db to Oracle? mysql is going to stau but I need to pass the data to oracle, just so the data can be transfered. I have carried out a mysql dump. This seems fine.create table etc. about 20 MB in total. Any ideas? It is on Redhat if that makes a difference. TIA -Syd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
export db to oracle
Hello, How can I export a mysql 5.0.45 db to Oracle? mysql is going to stau but I need to pass the data to oracle, just so the data can be transfered. I have carried out a mysql dump. This seems fine.create table etc. about 20 MB in total. Any ideas? It is on Redhat if that makes a difference. TIA -Syd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: export db to oracle
On 11/16/2010 15:14, Sydney Puente wrote: Hello, How can I export a mysql 5.0.45 db to Oracle? mysql is going to stau but I need to pass the data to oracle, just so the data can be transfered. I have carried out a mysql dump. This seems fine.create table etc. about 20 MB in total. Any ideas? It is on Redhat if that makes a difference. I suggest you also look at the syntax for SELECT INTO OUTFILE, too. Dumps are usually scripts of SQL statements that Oracle may not read appropriately. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
