[Oracle] テキストファイルの入出力方法

PL/SQLでプロシージャなどを作っていると、プロシージャから直接テキストファイルの入出力を行いたくなることがあります。

普通こういうことはアプリケーションでやるべきですが、バッチ的に処理したいという時にはプロシージャでという話になりがちです。

そういう要望が結構多いのでしょうか、Oracleにはテキストファイル入出力のためにUTL_FILEパッケージというものがちゃんと（？）用意されています。

UTL_FILEパッケージを利用したファイルの入出力の事前準備

UTL_FILEパッケージを使用してファイルの入出力を行うにはまず、初期化パラメータUTL_FILE_DIRの設定が必要です。

UTL_FILE_DIRはUTL_FILEパッケージがファイルの入出力先として使うディレクトリで、例えば

UTL_FILE_DIR=C:\Oracle

というように指定します。

これで指定したディレクトリへのファイルの入出力が可能になるわけですが、上記の設定とあわせて、Oracleインスタンスの実行ユーザー（Windowsの場合、サービスアカウント）が指定したディレクトリに対して適切なアクセス権限を持っている必要もあるので注意が必要です。

ただ、入出力先のディレクトリは1箇所に固定できるとは限りません。

そんな時には"*"を指定すれば、任意のディレクトリを使用することができるようになります。

ただし、セキュリティ的には"*"の設定は極力避けるべきでしょう。

（実際にはよく使っていますが^^;）

と、、ここまで書いてしまってなんなんですが、実は．．．Oracleは"*"以前にUTL_FILE_DIRで入出力ディレクトリを指定するのはやめましょうとか言ってたりします．．．

では、どうするのかというと、DIRECTORYオブジェクトを作って、それに適切な権限をGRANTせよと言うことだそうです。

なるほどOracleの言っていることはごもっともなのですが、DIRECTORYオブジェクトでは"*"指定ができないので、入出力ディレクトリが1箇所に特定できない場合はちょっと使いづらい（DIRECTORYを管理しきれない）です。

そんなわけで個人的にはセキュリティ面で問題が発生しないであろうと思われるような場合にはUTL_FILE_DIR=*という設定もありかなと勝手に解釈しているのですが、よい子はDIRECTORYオブジェクトを使ってください^^ゞ

UTL_FILEパッケージを利用したテキストファイルの入出力方法

さて、実際にUTL_FILEパッケージを利用してファイルの入出力を行う方法ですが、まぁ、例を挙げたほうが手っ取り早いだろうということで、以下にテキストファイルの出力例を挙げてみました。

 1  DECLARE
 2    wk_file  UTL_FILE.FILE_TYPE;
 3    CURSOR tbl_cur IS
 4      SELECT COLUMNA, COLUMNB FROM TABLEA;
 5    tbl_rec tbl_cur%ROWTYPE;
 6  BEGIN                  
 7    wk_file := UTL_FILE.FOPEN ('C:\Oracle\', 'OUTPUT.CSV', 'W', 32767);       
 8    OPEN tbl_cur;
 9    LOOP
10      FETCH tbl_cur INTO tbl_rec;
11      EXIT WHEN tbl_cur%NOTFOUND;                
12      UTL_FILE.PUT_LINE(wk_file, tbl_rec.COLUMNA || ',' || tbl_rec.COLUMNB); 
13    END LOOP;
14    CLOSE tbl_cur;
15    UTL_FILE.FFLUSH(wk_file);
16    UTL_FILE.FCLOSE(wk_file);
17  END;

まず、7行目でファイルをオープンします。

このときUTL_FILE.FOPENファンクションに指定する引数はディレクトリ・パス、ファイル名、オープン方法（r：読み込み、w：書き込み、a：追加書き込み）、1行あたり最大文字数の4つです。

最大文字数は指定しなければデフォルトで1000となりますので、1行の文字数が1,000を超える場合は、この引数の値を調整しなければなりません。

ちなみに32,767というのは最大値なので、これ以上長い行は出力不可能となります。

（1行が32kを超えるなんてまずありえないか^^;）

あと、ファイルをオープンした時に戻り値を受け取る変数（ファイル・ハンドル）ですが、UTL_FILE.FILE_TYPEというタイプで宣言した変数（2行目）でなければなりません。

次にファイルに行を出力しているのは12行目です。

このUTL_FILE.PUT_LINEプロシージャに指定する引数は2行目で取得したファイル・ハンドルとファイルに書き込む文字列です。

最後に16行目でファイルを閉じて（UTL_FILE.FCLOSEプロシージャ）と終わりなのですが、いきなりファイルを閉じると書き込み前にバッファリングされているデータが失われる可能性があるため、ファイルを閉じる前にUTL_FILE.FFLUSHプロシージャ（15行目）を実行し、バッファリングされているデータを強制的に書き込む必要があります。

なお、UTL_FILEパッケージで入出力するテキストデータの文字コードはデータベース・キャラクタセットとなります。

UTL_FILE.PUT_LINE_NCHARプロシージャやUTL_FILE.GET_LINE_NCHARプロシージャを使用すれば、Unicodeで入出力することもできますが、それ以外の文字コードでの入出力は不可能です。

UTL_FILEパッケージのその他の利用方法

UTL_FILEパッケージはテキストファイルの入出力の他、ファイルのコピー、リネーム、削除、RAWデータの入出力などちょっとしたファイルの操作も行うことができます。

詳細については「PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス」を参照のこと。

Archive Redo Blog

DBエンジニアのあれこれ備忘録

[Oracle] テキストファイルの入出力方法

UTL_FILEパッケージを利用したファイルの入出力の事前準備

UTL_FILEパッケージを利用したテキストファイルの入出力方法

UTL_FILEパッケージのその他の利用方法