Re: Importing from Oracle into MySQL
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