OracleでCSV形式のファイルを出力する

Pocket

株式会社ナレッジベースの杉山です。

色々な理由でデータベースのデータをCSV形式のファイルとして出力する要件があります。

Oracleの場合について検討してみました。

対象のシステムは、Oracle 12cで正確には12.1系です。

悔しいことに12.2系のSQL*PlusのSET MARKUPにCSV形式でファイル出力するためのオプションが追加されていました。

SQL*Plus ユーザーズ・ガイドおよびリファレンス(リリース12.2)

SQL*Plus ユーザーズ・ガイドおよびリファレンス(リリース12.1)

SQL*PlusでSETなどで出力形式を整形し、SELECTで強引にCSV形式にしてファイル出力も考えましたが、今一つ。

SQL Developerのエクスポート機能を使用してCSV形式のファイルを出力することも可能です。
数テーブルだけなら、これで対応しますが、かなりの数があります。
テーブルを一つ一つ手作業で対応することは時間がかかるし、データの整合性も取りたいので却下です。

仕方がないので、プロシージャを作成して対応することを検討しています。

作成したスクリプトは下記の通りです。

なんとなくCSV形式のファイルが出力されますが、BLOBが含まれるテーブルとかvarchar2の最大値を超えるようなデータが格納されているテーブルはダメですね。
ま、そんなテーブルを設計するのが問題あるような気がするので、とりあえずイレギュラー対応は別途検討します。

ちなみにCSVを出力するスクリプトをデータベース・オブジェクトとして格納しないように考えています。

set serveroutput on;
alter session set NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss';
alter session set NLS_TIMESTAMP_FORMAT='yyyy/mm/dd hh24:mi:ss.ff6';
declare
    -- 定数
    -- CSVファイルに出力するテーブル名のリスト
    para_file varchar2(100) := 'Table.txt';
    -- ファイルを読み書きするためのディリクトリ・オブジェクト
    dump_dir varchar2(100) := 'DUMP_DIR';

    -- 対象テーブル格納用リスト
    type t_list is table of varchar2(80) index by pls_integer;
    table_list t_list;

    -- 対象テーブルリストの取得
    function get_table_list(i_table_list varchar2, i_dir_name varchar2) return t_list as
        -- 対象テーブルリスト用
        fil utl_file.file_type;
        ret_list t_list;
        i integer := 0;
    begin
        -- テーブルリストファイルの内容をリストに格納
        fil := utl_file.fopen(i_dir_name, i_table_list, 'r', 32767);
        loop
            begin
                i := i + 1;
                utl_file.get_line(fil, ret_list(i));
            exception
                when NO_DATA_FOUND then
                    exit;
            end;
        end loop;
        utl_file.fclose(fil);
        return ret_list;
    exception
        when others then
            if utl_file.is_open(fil) then
                utl_file.fclose(fil);
            end if;
            raise;
    end;

    -- 対象テーブルのSELECT文生成
    -- i_table_name: テーブル名
    -- 戻り値: select文
    function get_sql_string(i_table_name varchar2) return varchar2 as
        cursor cur is
            select column_name from user_tab_columns where table_name = i_table_name order by column_id;
        rec cur%rowtype;
        dsql varchar2(32767);
    begin
        for rec in cur loop
            dsql := dsql || rec.column_name || ' ||'','' || ';
        end loop;
        dsql := 'select ' || substr(dsql, 0, length(dsql)-9) || ' as line from ' || i_table_name;
        return dsql;
    end;

    -- 対象テーブルのCSVファイル出力
    -- i_dump_dir: CSVファイル出力パス
    -- i_table_name: 出力対象テーブル名
    procedure put_csv_file(i_dump_dir varchar2, i_table_name varchar2) is 
        cur integer;
        val varchar2(32767);
        str varchar2(32767);
        ret integer;
        --ファイル出力用
        fil utl_file.file_type;
        file_name varchar2(256);
    begin
        -- 出力ファイルのオープン
        file_name := i_table_name || '.csv';
        fil := utl_file.fopen(i_dump_dir, file_name, 'w', 32767);

        -- 動的SQLのカーソル作成
        str := get_sql_string(i_table_name);
        cur := dbms_sql.open_cursor;
        dbms_sql.parse(cur, str, dbms_sql.native);
        dbms_sql.define_column(cur, 1, val, 32767);
        -- 動的SQLの実行
        ret := dbms_sql.execute(cur);
        -- カーソル・フェッチ
        loop
            if dbms_sql.fetch_rows(cur) > 0 then
                dbms_sql.column_value(cur, 1, val);
                utl_file.put_line(fil, val);
            else
                exit;
            end if;
        end loop;
        dbms_sql.close_cursor(cur);
        utl_file.fclose(fil);
    exception
        when others then
            dbms_output.put_line(str);
            if dbms_sql.is_open(cur) then
                dbms_sql.close_cursor(cur);
            end if;
            if utl_file.is_open(fil) then
                utl_file.fclose(fil);
            end if;
            raise;
    end;

    -- システム日時の取得
    function get_sysdate return varchar2 as
        ret varchar2(22);
    begin
        select to_char(sysdate) into ret from dual;
        return ret;
    end;
begin
    dbms_output.put_line('処理開始');
    table_list := get_table_list(para_file, dump_dir);
    for i in table_list.first .. table_list.last loop
        begin
            dbms_output.put_line('テーブル名:' || table_list(i) || ' 処理開始:' || get_sysdate);
            put_csv_file(dump_dir, table_list(i));
            dbms_output.put_line('テーブル名:' || table_list(i) || ' 処理終了:' || get_sysdate);
        exception
            when NO_DATA_FOUND then
                exit;
        end;
    end loop;
    dbms_output.put_line('処理終了');
end;
/
 

コメントを残す

メールアドレスが公開されることはありません。

CAPTCHA