株式会社ナレッジベースの杉山です。
色々な理由でデータベースのデータを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;
/
