中科院云平台架构师,专注于数字化、智能化,技术方向:云、Linux内核、AI、MES/ERP/CRM/OA、物联网、传感器、大数据、ML、微服务。
分类: Oracle
2013-07-04 18:46:56
UTL_FILE パッケージによって PL/SQL でファイルの入出力を行うことができる。
PL/SQL には の SPOOL に該当する簡略化した命令はない。大きなログを出力したい場合には、UTL_FILE パッケージを使用するかテーブルを使用する。標準出力へは を使用する。
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 XE を使用している場合には UTL_FILE の実行権限が付与されていないため別途権限を付与する必要がある。
に対して実行権限を付与するか個別のユーザに対して権限付与する。(どちらか一方でよい)
SQL> conn / as sysdba 接続されました。 -- PUBLIC (すべてのユーザ)に付与する場合 SQL> execute on utl_file to public; 権限付与が成功しました。 -- rivus (rivusユーザ)に付与する場合 SQL> execute on utl_file to rivus; 権限付与が成功しました。
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'
Oracle 9i 以降の場合 サーバー?パラメータ?ファイルを で編集する。
ALTER SYSTEM SET UTL_FILE_DIR='/u05/file_storage/recv_dir','/u05/file_storage/send_dir' SCOPE = SPFILE ;
(※) ディレクトリの指定のクォートは必須ではないが、ファイルのオープン時のトラブルを避けるために常にクォートすることをお勧めする。
Oracle に正しく設定できていない場合でも設定時にエラーにならない。実行時にが発生する。
設定の確認 ( Oracle 9i 以降であれば V$PARAMETER2 で複数行表示になっていることを確認する)
SELECT NAME, VALUE FROM V$PARAMETER2 WHERE NAME='utl_file_dir' ;
CREATE OR 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; /
ファイル書き込みで作成したファイルを読込先のディレクトリにコピーして実行。
CREATE OR 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); .PUT_LINE(vInput); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN .PUT_LINE('ファイルの終わりを検出しました'); END; UTL_FILE.FCLOSE(vHandle); EXCEPTION WHEN OTHERS THEN UTL_FILE.FCLOSE_ALL; RAISE; END; /
この制限により Oracle 10g 以降でなければ純粋なバイナリファイルを扱うことができない。
(※) 一度の書き込みで 32KB を超えることはできないが、RAW モードでの書き込みにおいては複数回に書き込みを分割することで1行が 32KB を超過することが可能である。
テキストモードで正しく扱えているように見えるサンプルも Web 上にあるが、厳密には内容が意図しないバイト列に書き換わっているため使用を避けた方がよいと思う。
例えば BLOB の内容を UTL_FILE.PUT_RAW を用いてファイル出力しても特定の用途(※1)と偶然性による特異なデータ配列(※2)にならない限り、そのファイルの一部が壊れてしまって価値がなくなるか、出力プログラムはエラーになる。
(※1) 一部のメディアファイルのフォーマットはファイルの一部にゴミデータが付与されていても再生?表示できる。
(※2) データのバイト列が 32K バイト連続して 0x0A(LF:改行)を含まない場合にはファイル書き込みエラーになる。
もし 32 KB 以内の RAW だけを出力する場合でもあっても最後のバッファをフラッシュ(※3) または、クローズすると OS 固有の改行コードが必ず付与されるため結局はバイナリデータとして扱うことができていない。
(※3) テキストモードでは UTL_FILE.FFLUSH を使用してもバッファに改行が含まれない場合には書き出されない仕様になっている。また含まれていている場合でも改行コード以前までの内容がフラッシュされる。CLOSE 時には最後に改行が自動的に付与される。