SQL*PlusでTSV出力

Oracle10g

Oracle内にあるデータをMySQLに移したくなりました。MySQLでLOAD DATA INFILEとか使えばいいわけですから、TSVで出力したいわけです。

「Oracle CSV TSV」とかでググれば腐るほど方法が出てきますが、完璧なものは皆無だと思います。というか、たぶんSQL*Plusを選ぶ時点で失敗です。仕事でやるなら「SI Object Browser」とかを使った方がいいと思います。どうせOracleを買う時点で予算は潤沢なんでしょ？

皮肉はさておき、僕のはまった内容を紹介します。

  • CSVは無理にしてもタブ区切りなら大丈夫だろうと思ったが、データにタブが含まれていた
  • それどころか、データに改行が含まれていた
    • データに改行が含まれていると、ネット上に落ちてるSQL*PlusでCSV吐く方法は大抵ダメです

で、どうしたかというと、

  1. 全テーブルを「SELECT * FROM xxx」した結果をspoolで記録
  2. テキストファイルをgrepして0x0bと0x0cがデータ内に無いことを確認

安全な文字が2個見つかりましたので、出力する方針が決まりました。

  • 区切り文字を0x0b（垂直タブ）にした
  • データ内の改行を0x0c（改ページ）にした
  • レコード区切り（行末）は0x0a。普通ですね。

これならSQL*Plusでも出力できるよ！具体的には次のようなファイルをSQL*Plusに食わせました。

set linesize 32767
set pagesize 0
set trimspool on
set colsep '^K'
set feedback off
set term off
set tab off
set flush off
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
spool /foo/bar/baz.tsv
select REPLACE(REPLACE(id,CHR(13),''),CHR(10),CHR(12)),REPLACE(REPLACE(col1,CHR(13),''),CHR(10),CHR(12)),… FROM table1;
spool off
exit

^Kは便宜的に書いただけで、0x0bの1文字です。CHR(11)とかは書けなかった気がしますので、頑張ってそういうキャラクタを書いてください。

また、最後のレコードがNULLになるとフィールド数が減ります。嫌だったらSELECT句の最後にダミーで,'1'とか書くと幸せになれるかもしれません。