Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

任意のテーブル、ビュー、SQL の結果を CSV で出力する

Table to CSV 、 View to CSV、 Select query to CSV を行うための PL/SQL パッケージ

CSV を出力する UNLOAD パッケージモジュール

Oracle においてテーブルから CSV を生成するための UNLOAD 機能は用意されていないため CSV 生成を行なうためのライブラリパッケージを作成。

以下のような要件のもと作成

• 任意のテーブル、ビュー、任意の SELECT 文、関数が使用可能
• DATE 型、TIMESTAMP 型の書式を指定可能
  デフォルト書式は SESSION の初期化パラメータ から自動設定(ALTER SESSION SET NLS_* 〜 に依存）
• フィールド間のセパレータを指定可能
• 文字列のクォート文字を指定可能
• 文字列データ内のクォート文字を別文字列に変換可能
• CHAR 型の文字データの空白埋め部分の TRIM 処理の有無を指定可能
• 数値型のクォートのありなしを指定可能
• CLOB データを抽出可能 ／ CLOB データを抽出するかしないかを選択可能

• 実行環境：Oracle 9i 以上
• ファイル出力はアプリケーション側で行なう （SQL*Plusなど）

CSV を生成するプログラム

USER_TABLES を ソートして CSV 化する例 その１

組み込んである GET_CSV 表関数 を使用して、SELECT 文だけで １カラムの CSV を取り出す。(１レコード 4000 バイト以下）

set pagesize 0
set linesize 300
select * from table(unload.get_csv(q'{select * from user_tables order by table_name}'));

q'{〜}' を使用すると SQL 文内に自然な形で単一引用符(')を使える ⇒ 代替引用符

USER_TABLES を ソートして CSV 化する例 その２

組み込んである GET_CSV_CLOB 表関数 を使用して、SELECT 文だけで １カラムの CSV を取り出す。(CLOB として抽出）

set pagesize 0
set linesize 300
set loboffset 1
set long 32767
set longchunksize 32767
select * from table(
  unload.get_csv_clob(q'{select * from user_tables order by table_name}')
);

カスタマイズして SQL*Plus から USER_TABLES を ソートして CSV 化する例

データソース（data_source）は テーブル名のみ、ビュー名のみ、または、任意の SQL が指定可能　
DATE 型の書式（date_format）は： YYYY年MM月DD日　⇒ date_format オプション
カンマ・セパレータではなく、タブ・セパレータファイル ⇒ set_col_sep オプション

set echo off
set feedback off
set term off
set head off
set linesize 1000
-- set linesize 2000
-- set linesize 4000
-- set linesize 32767
--  ↑↑ １レコードの文字列の長さによって適宜調整する（レコード数増加により相当遅くなる）
set long 32767
set longchunksize 32767
set pagesize 0
set trimspool on
set serveroutput on
var cur refcursor
begin
	:cur := unload.sql_to_csv(
		data_source => 'select * from user_tables order by table_name',
		date_format => 'YYYY"年"MM"月"DD"日"',
		set_col_sep => CHR(9)
	);
exception
 when others then
	dbms_output.put_line('ERROR : ' || SQLERRM);
	raise;
end;
/
set term off
spool user_tables.csv
print cur
spool off
set term on

高速化のポイント

SQL*Plus からレコード数が多いデータを UNLOAD するときの高速化のポイントは LINESIZE を取り出すデータに適した長さに設定すること。
不必要に SET LINESIZE のバイト数を大きくしすぎると処理時間が数倍〜十数倍にもなってしまう。
一歩踏み込んだ CSV ファイル出力の高速化手法 ⇒ CSV 出力を１０倍高速にする方法

Data UNLOAD パッケージ、SQL to CSV プロシージャ

PACKAGE 定義部のみなのでコピーしても動きません

UNLOAD 処理速度の目安

環境： Windows XP (Corei7-920) + Oracle 11g　
SQL*Plus から SPOOL にて CSV ファイル出力

テスト用 表定義 その１（やや大きめ）

CREATE TABLE BIG01
(
    V1                             VARCHAR2(200),
    N1                             NUMBER,
    N2                             NUMBER,
    D1                             DATE,
    T1                             TIMESTAMP(6),
    C1                             CHAR(50),
    V2                             VARCHAR2(200),
    V3                             VARCHAR2(200),
    V4                             VARCHAR2(200),
    V5                             VARCHAR2(200)
);

抽出レコード数： 100万レコード　／ 平均レコード長： 600 バイト の場合
ファイル出力時間： 約 5分 ／ ファイルサイズ 590 MB

テスト用 表定義 その２（やや小さめ）

CREATE TABLE SMALL01
(
    V1                             VARCHAR2(10),
    V2                             VARCHAR2(10),
    V3                             VARCHAR2(10),
    D1                             DATE
)

抽出レコード数 ： 10万レコード ／ レコード長 50 バイト の場合
ファイル出力時間： 約 15秒 ／ ファイルサイズ 5 MB

---

関連事項

• レコードデータを CSV 形式にする関数
• 文字列からトークンを切り出す
• CSV 出力を１０倍高速にする方法