テーブルのデータをCSV出力する

最近PL/SQLでプログラミングしてないと思ったので、リハビリを兼ねつつ、汎用的にテーブルのデータをCSV出力するプロシージャを作ってみる。

現在の環境
OS:Windows 7(Ultimate)
DB:Oracle11gXE


■１．権限付与

今回はadminというスキーマ内のテーブルをCSV出力するプロシージャを作ってみる。

出力先はXEが標準で持ってるDATA_PUMP_DIRディレクトリに出力しようと思うので、adminスキーマにDATA_PUMP_DIRのread, write権限を付与してみる。また出力に使うutl_fileの実行権限も一緒に付与。

SQL*PLUSでSYSでログインして下記を実行。

SQL>grant read, write on directory DATA_PUMP_DIR to admin;
SQL>grant execute on utl_file to admin;


■２．プロシージャの作成

次にadminスキーマにログインして、CSV出力するプロシージャ(csv_out)をコンパイル。

create or replace procedure csv_out (tbl_name in varchar2)
is
    select_str varchar2(2000);
    procedure create_select(tbl in varchar2, record_str out varchar2)
    is
        type rec_type is record(column_name varchar2(30), data_type varchar2(30));
        type tbl_rec_type is table of rec_type;
        tbl_rec tbl_rec_type;
    begin
        record_str := 'select ';
        execute immediate 'select column_name, data_type from user_tab_columns where table_name = :1 order by column_id' bulk collect into tbl_rec using tbl;
        for i in 1 .. tbl_rec.count loop
            record_str := record_str
                || case when tbl_rec(i).data_type in ('VARCHAR2', 'CHAR', 'CLOB') then ' ''"'' || ' else '' end
                || tbl_rec(i).column_name
                || case when tbl_rec(i).data_type in ('VARCHAR2', 'CHAR', 'CLOB') then ' || ''"'' ' else '' end
                || case when i=tbl_rec.last then '' else ' || '','' ||' end;
        end loop;
        record_str := record_str || ' as csv_data from ' || tbl;
    end;

    procedure create_csv(tbl in varchar2, str in varchar2)
    is
        fout utl_file.file_type;
        type csv_data_type is table of varchar2(5000);
        csv_data_tbl csv_data_type;
    begin
        fout := utl_file.fopen('DATA_PUMP_DIR', tbl || '.csv', 'w');
        execute immediate str bulk collect into csv_data_tbl;
        for i in 1 .. csv_data_tbl.count loop
            utl_file.put_line(fout, csv_data_tbl(i));
        end loop;
        utl_file.fclose(fout);
    end;
begin
  -- CSV出力Select文作成
  create_select(upper(tbl_name), select_str);
  -- CSV出力
  create_csv(upper(tbl_name), select_str);
end;
/

昔よくやっていた、CSV出力するためのSELECT文を作ってdbms_outputで出力するというのをPL/SQLに置き換えただけ…。
一応VARCHAR2,CHAR,CLOBだけ2重符号で囲う。


■３．実行

adminスキーマの中のテーブル(******)を適当に指定して、実行してみる。

SQL>execute csv_out('******');

出力先(C:\oraclexe\app\oracle\admin\XE\dpdum)に、******.csvというファイル名で出力されるので確認。



もっと簡単に出力できる方法があったりするんかな？調べてないけど…。というか、たいていCSV出力はsql_developerでやっちゃったりしてる(-_-;)。