MySQL Forums
Forum List  »  Oracle

Re: Importing from Oracle into MySQL
Posted by: David Beckedorff
Date: March 28, 2005 09:17PM

Hmmm. Pretty quiet thread.

Well, I figured out a better way. Use SQL*Plus to write a CSV file. Then use LOAD DATA INFILE to load it into MySQL.

Here's an example:

dump_managerlog.sql
---------------------

set termout off
set pagesize 0
set linesize 400
set feedback off

spool managerlog.dat

select
'\N' || ',' ||
'"' || to_char(ts, 'YYYY-MM-DD HH24:MI:SS') || '"' || ',' ||
'"' || trim(hostname) || '"' || ',' ||
'"' || trim(process_type) || '"' || ',' ||
process_id || ',' ||
'"' || trim(process_name) || '"' || ',' ||
'"' || trim(event_type) || '"' || ',' ||
'"' || to_char(event_ts, 'YYYY-MM-DD HH24:MI:SS') || '"' || ',' ||
nvl2(userid, '"'||trim(userid)||'"', '\N') || ',' ||
'"' || trim(session_id) || '"' || ',' ||
'"' || trim(job_family) || '"' || ',' ||
'"' || trim(job_family_detail) || '"' || ','
from
(select * from managerlog where rownum <= 25 order by ts desc)
order by ts asc;

spool off


The sub-select allows you to control the number of rows extracted and the sort order thereof.


Then to import that data, use:

mysql> load data local infile 'managerlog.dat' into table managerlog
-> fields terminated by ',' enclosed by '"'
-> lines terminated by '\r\n';
Query OK, 25 rows affected (0.00 sec)
Records: 25 Deleted: 0 Skipped: 0 Warnings: 0

mysql>


I'm running on Windows XP, so the EOL needs to be '\r\n'.

One semi-weird thing -- the final column has to be terminated by a ','; otherwise the string value in the last column doesn't get parsed correctly. That seems to be downright user-unfriendly, like most UNIXes and unlike Windows.

One other thing: you need to specify a long line size to SQL*Plus to get each row on one line, but that leaves whitespace at the end of each line. The 'mysql' client barfs on that, so you need to clean up the spaces in an editor before trying to load the data (you know, sed -e 's/, *$/,/' or the equivalent).

If you have errors, you get a count of "warnings" and absolutely no information about what's wrong. Tough love, right? Open source, right? There definitely is some room for contributors here.

But anyway, it works, and runs quite fast.

-David

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Importing from Oracle into MySQL
11080
March 28, 2005 09:17PM
3981
January 22, 2007 12:48AM
4609
March 30, 2005 06:13PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.