テーブルのデータをCSV出力する（PL/SQLでやってみる）

これは試行錯誤の途中経過です。まとめ→http://d.hatena.ne.jp/irof/20100108/p2

- - -

「余計なスペースが入って欲しくない場合」かつ「対象テーブルまたは列が沢山ある場合」で、力技なんてやってられない場合のやり方。

この場合は、諦めて出力するツールを使いたい所だけれども、許可されていなかったり、面倒だったり、微妙なカスタマイズが出来なかったりして困る場合があります。標準で出力できて良い気もしますが、軽く調べただけでは見当たりませんでした。前述のようなやり方が紹介されているくらいなので、無いのでしょう。
この条件で使える物を考えてみると、Oracleなので、PL/SQLが使えます。列名ならUSER_TAB_COLUMNSがあります。なら出来るじゃないか、と~~仕事中の待ち時間が暇だったので~~書いてみた。

set serveroutput on
set feedback off
set verify off
set trimspool on
set linesize 32767

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
prompt **********************************************************************
prompt &_DATE CSVファイル出力を開始します。
set term off

spool '&2'
DECLARE
    vTableName VARCHAR2(30) := '&1';
    vColumns VARCHAR2(32767);
    vSelectSql VARCHAR2(32767);
    vFirstFlag BOOLEAN := TRUE;

    TYPE cusorType IS REF CURSOR;
    c cusorType;
    vOutputLine VARCHAR2(32767);
BEGIN
    vColumns := NULL;
    vSelectSql := 'SELECT ';
    FOR vRec IN (
        SELECT COLUMN_NAME
        FROM USER_TAB_COLUMNS
        WHERE TABLE_NAME = UPPER(vTableName)
        ORDER BY COLUMN_ID
    ) LOOP
        IF NOT(vFirstFlag) THEN
            vColumns := vColumns || ',';
            vSelectSql := vSelectSql || ' || '','' || ';
        ELSE
            vFirstFlag := FALSE;
        END IF;
        vColumns := vColumns || vRec.COLUMN_NAME;
        vSelectSql := vSelectSql || vRec.COLUMN_NAME;
    END LOOP;
    vSelectSql := vSelectSql || ' FROM ' || vTableName;

    DBMS_OUTPUT.PUT_LINE(vColumns);
    OPEN c FOR vSelectSql;
    LOOP
        FETCH c INTO vOutputLine;
        EXIT WHEN c%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(vOutputLine);
    END LOOP;
    CLOSE c;
END;
/
spool off

set term on
prompt &_DATE CSVファイル出力が完了しました。（ファイル名：&2）
prompt **********************************************************************
exit

実行コマンドはこんな感じ。上記の内容を「OutputCsv.sql」の名前で保存し、そのディレクトリでSQL*Plusを実行する。tablenameはテーブル名、output.csvは出力ファイル名。

sqlplus -s user/pass @OutputCsv.sql tablename output.csv

サイレントモードにしているのは、接続されましたとかのログが邪魔っぽかったからです。実行結果はこんな感じになります。

C:\work>sqlplus -s user/pass @CsvOutput.sql tablename output.csv
**********************************************************************
2010-01-07 23:03:43 CSVファイル出力を開始します。
2010-01-07 23:03:43 CSVファイル出力が完了しました。（ファイル名：output.csv）
**********************************************************************

C:\work>

複数テーブル出したいなら並べてしまえば良いです。ユーザ名は対象テーブルが入っているスキーマにしないといけません。USER_TAB_COLUMNSを使っているので。ALL_TAB_COLUMNSにしてOWNERを条件に入れ、テーブル名をスキーマ修飾すれば良いのですが、パラメータが多くなると面倒なので。

sqlplus -s user/pass @OutputCsv.sql tablename1 output1.csv
sqlplus -s user/pass @OutputCsv.sql tablename2 output2.csv
sqlplus -s user/pass @OutputCsv.sql tablename3 output3.csv
sqlplus -s user/pass @OutputCsv.sql tablename4 output4.csv
sqlplus -s user/pass @OutputCsv.sql tablename5 output5.csv

【注意点】
10gR2以降専用です。
古いバージョンでは、DBMS_OUTPUTで出力できるサイズの制限*1 *2に引っかかる可能性があるので、使い物にならないかもしれません。それでも使いたい場合、&_DATEを消した上で、とりあえずバッファサイズを最大にしてみて実行してみてください。それで駄目なら、UTL_FILEを使うとか、一時テーブルに格納するとかしてください。どちらにせよDBサーバ自体をいじる事になります。
対象テーブルが見当たらない場合はエラーになりますが、画面上は表示してくれません。出力ファイルをみれば駄目だった事は判ります。でも駄目だった事しか判りません。例外処理に力を入れるべきものでもないので、先にテーブルの存在くらいは別ルートで確認すること。出力ファイルの内容は以下のような感じになります。

DECLARE
*
行1でエラーが発生しました。:
ORA-00936: 式がありません。
ORA-06512: 行31

項目数が多いとかデータ形式によっては使えない可能性も十分あります。DATA_TYPEとかDATA_LENGTHとかを見て、取得列を絞るようにすればそのようなテーブルでも使えるようにはなりますが、使う対象として想定していないので気にしない事にします。

*1:1行あたり255バイト。全体で最大1000000バイト。サイズは set serveroutput on size n で指定する。

*2:10gR2で1行あたり32767バイト、全体では無制限に拡張された。

日々常々

ふつうのプログラマがあたりまえにしたいこと。

テーブルのデータをCSV出力する（PL/SQLでやってみる）