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

UTL_FILE パッケージの使い方 (UTL_FILE_DIR 編)

UTL_FILE パッケージによって PL/SQL でファイルの入出力を行うことができる。
PL/SQL には SQLPlus の SPOOL に該当する簡略化した命令はない。大きなログを出力したい場合には、UTL_FILE パッケージを使用するかテーブルを使用する。標準出力へは DBMS_OUTPUT パッケージを使用する。

Oracle 8i 以前では初期化パラメータ UTL_FILE_DIR を設定する必要がある。この指定方法には FAQ 的な誤りが良くある。ここで使い方を簡単に整理しておく。

準備作業

ディレクトリの作成

ディレクトリを作成するときに OS の DBA であるユーザー(通常は oracle)でディレクトリを作成する。

初期化パラメータを使用する場合に UTL_FILE_DIR の指定方法に良く誤りがあるので、ディレクトリは 2つ作成しておく。 root で作成する場合には、chown、chgrp、chmod などによって読み書きの権限を正しく設定する。
ファイルの I/O はサーバープロセスによって行われるため、そのプロセスのオーナ(oracle)が読み書きできる必要がある。

作成と権限の設定例 (root による操作:権限さえあれば root である必要はありません)

# mkdir /u05/file_storage/recv_dir
# mkdir /u05/file_storage/send_dir

# chgrp dba /u05/file_storage/recv_dir
# chgrp dba /u05/file_storage/send_dir
# chown oracle /u05/file_storage/recv_dir
# chown oracle /u05/file_storage/send_dir

# chmod 700 /u05/file_storage/recv_dir
# chmod 700 /u05/file_storage/send_dir

Oracle Database XE(eXpress Edition) と UTL_FILE パッケージ

Oracle XE を使用している場合には UTL_FILE の実行権限が付与されていないため別途権限を付与する必要がある。

PUBLIC に対して実行権限を付与するか個別のユーザに対して権限付与する。（どちらか一方でよい）

SQL> conn / as sysdba
接続されました。
-- PUBLIC （すべてのユーザ）に付与する場合
SQL> grant execute on utl_file to public;
 
権限付与が成功しました。
 
-- rivus （rivusユーザ）に付与する場合
SQL> grant execute on utl_file to rivus;
 
権限付与が成功しました。

初期化パラメータ UTL_FILE_DIR によるファイルアクセス

初期化パラメータ UTL_FILE_DIR の設定

Oracle 8i 以前の UTL_FILE パッケージでは、CREATE DIRECTORY には対応していない。 UTL_FILE_DIR 初期化パラメータによる画一的な I/O にのみ対応する。

UTL_FILE_DIR 初期化パラメータを指定する方法は情報の改ざんや漏洩の危険性がある。
Oracle 8i 以前で動作する必要がない場合には、UTL_FILE_DIR 初期化パラメータによるファイルアクセスは行わないようにした方がよい。

Oracle 8i 以前の場合、初期化パラメータ・ファイル を編集する。
initORACLE_SID.ora を編集する。

UTL_FILE_DIR='/u05/file_storage/recv_dir','/u05/file_storage/send_dir'

• 関連事項
  サーバー・パラメータ・ファイル (SPFILE)、パラメータ・ファイル (PFILE) の探索順

Oracle 9i 以降の場合 サーバー・パラメータ・ファイルを ALTER SYSTEM コマンド で編集する。

ALTER SYSTEM SET
  UTL_FILE_DIR='/u05/file_storage/recv_dir','/u05/file_storage/send_dir'
SCOPE = SPFILE ;

UTL_FILE_DIR パラメータ (FAQ)

• UTL_FILE_DIR 初期化パラメータの変更を適用するには Oracle の再起動が必要。
• 読み書きできるのは UTL_FILE_DIR 初期化パラメータで指定したディレクトリのみで下層のディレクトリでもアクセスできない。
• ディレクトリ名の末尾にディレクトリのデリミタのスラッシュ(/)や円マーク(¥)は付けない*1。
• 複数のディレクトリを指定する場合には、個々をクォートしてからカンマで区切って指定する。(※)
• UTL_FILE_DIR による制限をすべて解除する場合には アスタリスク('＊') を指定する。
  この設定は本番環境において、ほとんどケースで OS レベルでも非常に危うい設定であり、Oracle 的には間違いなく危険といえる。テスト環境のみに使用し、本番運用環境では絶対に避けることをお勧めする。⇒ ファイルセキュリティ

(※) ディレクトリの指定のクォートは必須ではないが、ファイルのオープン時のトラブルを避けるために常にクォートすることをお勧めする。
Oracle に正しく設定できていない場合でも設定時にエラーにならない。実行時にORA-29280 エラーが発生する。

設定の確認 ( Oracle 9i 以降であれば V$PARAMETER2 で複数行表示になっていることを確認する)

SELECT NAME, VALUE FROM V$PARAMETER2 WHERE NAME='utl_file_dir' ;

FOPEN 〜 PUT_LINE ファイル書き込み（初期化パラメータ使用時）

CREATE OR REPLACE PROCEDURE RIVUS.UTL_FILE_DIR_WRITE_SAMPLE
AS
	vHandle		UTL_FILE.FILE_TYPE;
	vDirname	VARCHAR2(250);
	vFilename	VARCHAR2(250);
	vOutput		VARCHAR2(32767);
BEGIN
	vDirname := '/u05/file_storage/send_dir';   -- ディレクトリの絶対パス名を書く
	vFilename := 'test.txt';
	vHandle := UTL_FILE.FOPEN(vDirname ,vFilename,'w', 32767);
 
	vOutput := '初期化パラメータ UTL_FILE_DIR 経由でのファイル出力です';
	UTL_FILE.PUT_LINE(vHandle, vOutput);
	UTL_FILE.FCLOSE(vHandle);
EXCEPTION WHEN OTHERS THEN
	UTL_FILE.FCLOSE_ALL;
	RAISE;
END;
/

GET_LINE ファイル読み込み ( 初期化パラメータ使用時 )

ファイル書き込みで作成したファイルを読込先のディレクトリにコピーして実行。

CREATE OR REPLACE PROCEDURE RIVUS.UTL_FILE_DIR_READ_SAMPLE
AS
	vHandle		UTL_FILE.FILE_TYPE;
	vDirname	VARCHAR2(250);
	vFilename	VARCHAR2(250);
	vInput		VARCHAR2(32767);
BEGIN
	vDirname := '/u05/file_storage/recv_dir';
	vFilename := 'test.txt';
	vHandle := UTL_FILE.FOPEN(vDirname ,vFilename,'r', 32767);
 
	BEGIN
		LOOP
			UTL_FILE.GET_LINE(vHandle, vInput,32767);
			DBMS_OUTPUT.PUT_LINE(vInput);
		END LOOP;
	EXCEPTION
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('ファイルの終わりを検出しました');
	END;
	UTL_FILE.FCLOSE(vHandle);
EXCEPTION WHEN OTHERS THEN
	UTL_FILE.FCLOSE_ALL;
	RAISE;
END;
/

注意事項

• UTL_FILE パッケージで使用できる、一度に入出力できる長さは 32767(32K) バイト (※)
• OPEN したファイルは必ず CLOSE するように例外処理を施しておく。UTL_FILE.FCLOSE_ALL を使うと便利
• Oracle 11g からシンボリックリンクのファイルは利用できない。
• RAW 型を出力することはできるが行末には OS 固有の改行コードが必ず付与される。
  Oracle 10g では wb による (RAW モード) FOPEN がサポートされているので、そちらを使うと問題ない。

この制限により Oracle 10g 以降でなければ純粋なバイナリファイルを扱うことができない。

(※) 一度の書き込みで 32KB を超えることはできないが、RAW モードでの書き込みにおいては 複数回に書き込みを分割することで１行が 32KB を超過することが可能である。

テキストモードで正しく扱えているように見えるサンプルも Web 上にあるが、厳密には内容が意図しないバイト列に書き換わっているため使用を避けた方がよいと思う。

例えば BLOB の内容を UTL_FILE.PUT_RAW を用いてファイル出力しても特定の用途(※1)と偶然性による特異なデータ配列(※2)にならない限り、そのファイルの一部が壊れてしまって価値がなくなるか、出力プログラムはエラーになる。

(※1) 一部のメディアファイルのフォーマットはファイルの一部にゴミデータが付与されていても再生・表示できる。

(※2) データのバイト列が 32K バイト連続して 0x0A(LF:改行)を含まない場合にはファイル書き込みエラーになる。

もし 32 KB 以内の RAW だけを出力する場合でもあっても最後のバッファをフラッシュ(※3) または、クローズすると OS 固有の改行コードが必ず付与されるため結局はバイナリデータとして扱うことができていない。

(※3) テキストモードでは UTL_FILE.FFLUSH を使用してもバッファに改行が含まれない場合には書き出されない仕様になっている。また含まれていている場合でも改行コード以前までの内容がフラッシュされる。CLOSE 時には最後に改行が自動的に付与される。

---

ファイルI/O 関連事項

• PL/SQL でバイナリファイルを書き込む方法
• ファイルアクセスのセキュリティ
• データベースの改行コードを取得する
• RAISE
• ALTER SYSTEM による初期化パラメータの変更