タイトルロゴ

記載日 2006/02/18
はじめに

 PL/SQLは、SQLを拡張したプログラミング言語です。 手続き処理機能があり、変数・定数宣言、データ型宣言、制御文、例外処理などを記載することができます。

 一般的には、プロシジャーファンクショントリガーの開発に使用される言語ですが、 無名PL/SQLブロック (※「ローカルなサブプログラム」とも言います。DECLAREキーワードから始まる名前の付かないPL/SQLブロックを指します。)を使用して データ操作に利用するデータベース開発者・管理者もいます。 C言語やC++、Java、VBなど、各種言語を経験した開発者ならば、きっと、とっつきやすい言語だと思います。

 データベースにオラクルを使用しているシステムの大半でPL/SQLを利用している様です。

 決して高速な言語とは言えませんが、細かく何度もSQLを発行するアプリケーションがある場合、 そのSQLを順番に一つずつ送信する通信量を考えると、それらのSQLを一つのPL/SQLブロックにまとめ、 そのPL/SQLブロックを送信する方が通信量が大幅に減りパフォーマンスが上がります。 (※誤解してほしくありませんが、通信量が改善されると言うだけです。 実行するSQL文が、PL/SQLブロック化の前後で同じであれば、 そのSQL文のパフォーマンス改善を期待してはいけません。)

 PL/SQL言語で開発をしていると、処理の分岐ポイントや、例外発生時のエラー情報をログに残したい と言う欲求にかられますよね。実運用レベルで、 PL/SQL言語におけるログ出力方法をどの様に実装したらよいか考えてみたいと思います。



セクション

















検証環境

 
ページのTOPへ

 システム開発におけるログ出力機能の役割は重要です。 中でも、エラー発生時のログ出力は、「本来の機能よりもエラー処理の方が重要である。」 と言われるくらい重要視されているようです。いかなるシステムであっても、ログ出力機能は必要不可欠であり、 ログ出力機能の存在しないシステムは、システムではなく、ツールと言う格付になるのではないでしょうか。

  ログ出力機能が必要とされる理由は何でしょうか?下に挙げた四つの内容は、私が勝手に思いついたもの書き並べただけです。 考えればいろいろ出てきそうです。

<ログ出力機能の必要性>
 1  開発段階でデバッグする為。
 2  エラー発生時に原因究明する為。
 3  システム試運転中、処理時間を計測する為。
 4  システム運用中に、誰が何時、どのようなオペレーションを実施したのか、作業履歴を残す為。


 次に、ログ出力機能に実装されているべき機能についても考えてみました。これまた、私が勝手に思いついたものを 挙げましたが、ログ出力機能に何を求めるかで、各々プログラマの個性が出るのだと思います。

<ログ出力機能の必要条件>
 1  出力されたログを時系列で参照できるように、出力形式に必ず時刻を表示すること。
 2  ログ出力に要する時間が、本筋機能の処理時間を圧迫することがあってはいけない。
 3  ログの世代管理・保存期間の設定が間単に行える形式で、ログ出力形式が整理されていること。
 4  複数プロセスによる同時ログ出力に耐えうること。
 5  ログ出力機能自体のエラーによって本筋機能が停止することがあってはいけない。


 最後に、ログ出力をどの様な箇所で行うか考えてみました。下記のNo.5 は特に重要です。 前述した「ログ出力機能の必要条件」の 2 「ログ出力に要する時間が、本筋機能の処理時間を圧迫することがあってはいけない。」 を意識して、繰返し何度も呼ばれる細かなサブルーチンやループ処理の 中でログ出力を行う際は十分注意しましょう。

<ログ出力機能の埋め込み方針>
 1  エラー発生時にエラー内容をログ出力する。
 2  処理時間が長くかかるプログラムの開始、終了に計測目的のログ出力を行う。
 3  デバッグ用に入れたログ出力を残したまま、プログラムをリリースしない。
 4  処理の開始時、終了にログ出力を行う。
 5  繰返し何度も実行される箇所ではログ出力を行わない。もしくは、n 回に一回の割合で出力する。



 
ページのTOPへ

 ログとしてどの様な情報を出力すれば良いのか、我流ですが、以下に整理してみました。

<ログに必要な出力情報>
 1  年月日時分秒  PL/SQLからログ出力する際には、一般的にはsysdate関数current_timestamp関数を利用します。

※DATE型やTIMESTAMP型を明示的に文字列に変換したい場合は、
 ・TO_CHAR(SYSDATE,YYYY/MM/DD HH24:MI:SS')
 ・TO_CHAR(CURRNET_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF') を使用します。
 2  実行ユーザ名  誰が、何を実行したのかログから判断できるようになります。
 3  プログラムID  PL/SQLのプログラムを識別するプログラムIDがあれば、それを出力することで、 どのプログラムから生成されたログなのか判別しやすくなります。 処理ID、機能IDなどを定義して開発を進めているチームであれば、 それらを出力すれば良いでしょう。パッケージ名、プロシジャー名、ファンクション名でも良いと思います。 プログラム的問題が問題した場合、ログを見てどの機能担当者に解析を依頼すれば良いか判断しやすくなります。
 4  メッセージ区分  ログ出力のメッセージを分類したキーワードがあれば便利だと思います。それらキーワードを使用して、 大量に作成されたログから、情報を抽出しやすくなります。 「3 プログラムID」 と組み合わせれば、例えば、 「帳票出力機能から発せられているエラーメッセージだけをログから抽出したい。」など、ファイルに対する grep操作だけで、ログの絞込みが可能になります。

[キーワード例]
 ・デバッグメッセージ…"DBUG"、"DBG"、"D"
 ・情報メッセージ…"INFORMATION"、"INF"、"M" ※"I"だと、数字の"1"と判断し辛い
 ・警告メッセージ…"WARNING"、"WAR"、"W" ※本当は"WAR"と言う単語を避けたい。
 ・エラーメッセージ…"ERROR"、"ERR"、"E"
 5  メッセージID  ログ出力のメッセージ本文にメッセージIDを割振って開発している場合は、それを表示します。デバッグメッセージの場合は、 何も表示しなくて良いでしょう。
 メッセージが出力されるに至った原因や、対処方法をまとめたメッセージマニュアルがあれば、このメッセージIDが役に立ちます。
 6  メッセージ本文  ログ出力したいメッセージ本文。



 
ページのTOPへ

 PL/SQLにおいて、ログ出力機能をどの様に実装していますか?考えられる実装方法を四つあげてみます。

  ■ 2-1) テーブルを利用した実装方法
 お手製のログ保管テーブルを用意して、PL/SQLの処理中に、そのテーブルへINSERT文を発行し、ログをレコードとして残す方法です。この時、 sysdate関数currrent_timestamp関数を用いてタイムスタンプを 記録することになるでしょう。

 PL/SQLの処理が終わったところで、ログ保管テーブルにSELECT文を発行すればログを確認することができます。

 詳細は後続のセクションで説明しますが、この方針のポイントは、ログ出力機能を実装する プロシジャー/ファンクションを「自律型トランザクション」 として作成するところにあります。

 この方法をシステムの実運用で実装する場合は、「ログ保管テーブル内のデータ保存期間と削除タイミング」 について考える必要があります。

  

  ■ 2-2) DBMS_OUPUTパッケージを利用した実装方法
 PL/SQLの処理中に、DBMS_OUTPUT.PUTDBMS_OUTPUT.PUT_LINEを用いて、 ログをバッファに溜め込み、処理の最後にメッセージとして画面に表示する方法です。 SQL*Plusでしか使えません。このコンテンツの目的であるログを残すと言う 意味からは外れるかもしれません。

 開発者のデバッグ時に使用されることが多いと思います。デバッグ用の標準出力が簡単に実施できます。ファイルへの入出力機能を兼ね備えているわけではないので、 システムとして提供するログ出力機能に利用できるものではありません。

 詳細は後続のセクションで説明しますが、「DBMS_OUTPUT.PUT_LINEの出力結果が表示されるタイミング」「DBMS_OUTPUT.PUT_LINEで利用できるバッファの上限」の二点について事前に知っておく必要があります。

  

  ■ 2-3) UTL_FILEパッケージを利用した実装方法
 Oracleの組み込みパッケージであるUTL_FILEパッケージから、 ファイル入出力を行う機能が提供されています。これを利用することでログをファイルに出力することができます。

 UTL_FILEパッケージを使用する際に、Oracle8i迄は、初期化パラメータ"UTL_FILE_DIR"を設定する必要がありました。 この初期化パラメータにはセキュリティー上の問題があり、UTL_FILEパッケージの使用はテスト環境に留まるべきだと 言われ、システム運用中の使用は御法度とされてきました。Oracle9i以降ではCREATE DIRECTORYの登場により、セキュリティー上の問題が若干改善 されました。

 詳細は後続のセクションで説明しますが、「初期化パラメータ"UTL_FILE_DIR"を使用した際のセキュリティー上の 問題点」を把握しておく必要があります。完璧なセキュリティーを確保することは難しいことではありますが、 初期化パラメータ "UTL_FILE_DIR"の使用方法は、今の時代に明らかに合っていません。Oracle9i以降のバージョンであれば、 CREATE DIRECTORYコマンドを利用した方法でUTL_FILEパッケージを使用するようにしましょう。

 この方法をシステムの実運用で実装する場合は、ログ出力ファイルの「保存期間、世代管理方法」を考える必要があります。

  

  ■ 2-4) Javaのソースをデータベースに取り込みログ出力クラスを利用した実装方法
 データベースにJavaのソースを保存し、PL/SQLからJavaのメソッドを呼出すことができます。 Javaでログ出力機能を実装し、PL/SQLから、そのログ出力機能を Javaプログラムへのラッパープロシジャー/ファンクションを呼出すだけで 利用することができます。

 詳細は後続のセクションで説明しますが、この方針のポイントは、「PL/SQLからJavaプログラムを呼出す方法」「ログ出力先となるファイルに対するアクセス許可」にあります。

 この方法をシステムの実運用で実装する場合は、ログ出力ファイルの「保存期間、世代管理方法」を考える必要があります。

  



 
ページのTOPへ

 実際に、PL/SQLの処理中に、ログ保管テーブルにINSERT文を発行してログをレコードとして残す方法を検証してみます。

 ログとして残す情報は、「§2) ログ出力時に出力する内容」であげたものをそのまま使用します。 これに従って、ログ保管テーブルを次のように定義したいと思います。

ログ保管テーブル
 No.   COLUMN   TYPE   PK   IX   NL   説明 
 1  タイムスタンプ TIMESTAMP
 2  実行ユーザ名 VARCHAR2(10)
 3  プログラム名 VARCHAR2(30)
 4  メッセージ区分 CHAR(1) 1 CHECK IN ('D','M','W','E')
 5  メッセージID CHAR(9) "TEST-NNNN"のフォーマットで固定長文字列
 6  メッセージ本文 VARCHAR2(300)

   CREATE TABLE文は、以下のようになります。
CREATE TABLE  ← こちらをクリック(※使用する場合は、スクリプト中の表領域名を実行環境に合わせて書換える必要があります。)

/* -------------------------------- */
/* テーブル定義                     */
/* -------------------------------- */
/* ログ保管テーブル */
CREATE TABLE LOG_SAVE_T(
      TIME_STMP  TIMESTAMP
    , USER_ID  VARCHAR2(10)
    , PRG_NAME  VARCHAR2(30)
    , MSG_SECT  CHAR(1)
    , MSG_ID  CHAR(9)
    , MSG  VARCHAR2(300)
, CONSTRAINT MSG_SECT_CHK CHECK(MSG_SECT IN ('D','M','W','E'))
) TABLESPACE テーブルデータ表領域;
/* 主キー・インデックス */
CREATE INDEX LOG_SAVE_IDX1 ON LOG_SAVE_T(MSG_SECT) TABLESPACE インデックス表領域;
/* テーブルコメント */
COMMENT ON TABLE LOG_SAVE_T IS 'ログ保管テーブル';
/* テーブルカラムコメント */
COMMENT ON COLUMN LOG_SAVE_T.TIME_STMP IS 'TIMESTAMP型';
COMMENT ON COLUMN LOG_SAVE_T.USER_ID IS 'VARCHAR2(10)型';
COMMENT ON COLUMN LOG_SAVE_T.PRG_NAME IS 'VARCHAR2(30)型';
COMMENT ON COLUMN LOG_SAVE_T.MSG_SECT IS 'CHAR(1)型 INDEX有 説明:CHECK IN (''D'',''M'',''W'',''E'')';
COMMENT ON COLUMN LOG_SAVE_T.MSG_ID IS 'CHAR(9)型 説明:"TEST-NNNN"のフォーマットで固定長文字列';
COMMENT ON COLUMN LOG_SAVE_T.MSG IS 'VARCHAR2(300)型 NOT NULL';
/

 次に、ログ保管テーブルにログ情報をINSERTするパッケージを考えます。  CREATE PACKAGE文は、以下のようになります。
CREATE PACKAGE  ← こちらをクリック

/* -------------------------------- */
/* ログ出力パッケージ定義           */
/* -------------------------------- */
CREATE OR REPLACE PACKAGE PLSQLLOGTEST_01_P
AS
    /* ------------------------------------------ */
    /* ログ出力関数                               */
    /* 引 数:IP_TIME_STMP TIMESTAMP             */
    /*       :IP_USER_ID VARCHAR2                */
    /*       :IP_PRG_NAME VARCHAR2               */
    /*       :IP_MSG_SECT VARCHAR2               */
    /*       :IP_MSG_ID VARCHAR2                 */
    /*       :IP_MSG VARCHAR2                    */
    /* 戻り値:正常終了  0                       */
    /*       :異常終了 -1                       */
    /* 説 明:自律型トランザクション             */
    /* ------------------------------------------ */
    FUNCTION PLSQLLOGGING(
          IP_TIME_STMP TIMESTAMP
        , IP_USER_ID VARCHAR2 
        , IP_PRG_NAME VARCHAR2
        , IP_MSG_SECT VARCHAR2
        , IP_MSG_ID VARCHAR2
        , IP_MSG VARCHAR2
    ) RETURN NUMBER;

END PLSQLLOGTEST_01_P;
/
CREATE OR REPLACE PACKAGE BODY PLSQLLOGTEST_01_P
AS
    /* ------------------------------------------ */
    /* 変数・定数宣言                             */
    /* ------------------------------------------ */
    RETURN_NORMAL  CONSTANT NUMBER := 0; /* パッケージ内のFUNCTIONが正常終了した場合の戻り値 */
    RETURN_ERROR  CONSTANT NUMBER := -1; /* パッケージ内のFUNCTIONが異常終了した場合の戻り値 */

    /* ------------------------------------------ */
    /* ログ出力関数                               */
    /* 引 数:IP_TIME_STMP TIMESTAMP             */
    /*       :IP_USER_ID VARCHAR2                */
    /*       :IP_PRG_NAME VARCHAR2               */
    /*       :IP_MSG_SECT VARCHAR2               */
    /*       :IP_MSG_ID VARCHAR2                 */
    /*       :IP_MSG VARCHAR2                    */
    /* 戻り値:正常終了  0                       */
    /*       :異常終了 -1                       */
    /* 説 明:自律型トランザクション             */
    /* ------------------------------------------ */
    FUNCTION PLSQLLOGGING(
          IP_TIME_STMP TIMESTAMP
        , IP_USER_ID VARCHAR2 
        , IP_PRG_NAME VARCHAR2
        , IP_MSG_SECT VARCHAR2
        , IP_MSG_ID VARCHAR2
        , IP_MSG VARCHAR2
    ) RETURN NUMBER AS
        PRAGMA AUTONOMOUS_TRANSACTION;  /* 自律型トランザクション */
    BEGIN
        /* ログ保管テーブルにデータをINSERTする。*/
        INSERT INTO LOG_SAVE_T(
                          TIME_STMP
                        , USER_ID
                        , PRG_NAME
                        , MSG_SECT
                        , MSG_ID
                        , MSG
                        )
                        VALUES(
                          IP_TIME_STMP
                        , IP_USER_ID
                        , IP_PRG_NAME
                        , IP_MSG_SECT
                        , IP_MSG_ID
                        , IP_MSG
                        );
        COMMIT;
        RETURN RETURN_NORMAL;
    /* エラーは全て無視 */
    EXCEPTION
        WHEN OTHERS THEN
            RETURN RETURN_ERROR;
    END PLSQLLOGGING;
END PLSQLLOGTEST_01_P;
/

 自律型トランザクションとして作成しているところがポイントです。これは、呼出し元から独立したトランザクション で実行される関数として宣言していることを表しています。この宣言により、呼出し元でエラーが発生して、ROLLBACKが実施 された場合であっても、ログ保管テーブルへのINSERTは、別トランザクションとして呼出元と関係なくCOMMITされる仕組みになっています。

 上記のログ保管テーブルを作成し、ログ出力パッケージをコンパイルした環境で、自律型トランザクションの動きを 確認した例を紹介します。

SQL> DECLARE
  2      RET NUMBER := 0;  /* PLSQLLOGTEST_01_P.PLSQLLOGGINGの戻り値を格納 */
  3  BEGIN
  4      RET := PLSQLLOGTEST_01_P.PLSQLLOGGING(
  5                    CURRENT_TIMESTAMP
  6                  , 'NICEUSER'
  7                  , 'SAMPLE'
  8                  , 'D'
  9                  , 'TEST-0000'
 10                  , '自律型トランザクションの確認'
 11              );
 12      ROLLBACK;  --// ← ログ出力関数を呼出した直後にROLLBACKを発行する。
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM LOG_SAVE_T;  --// ↓ それでも、ログ保管テーブルにレコードが入っている。
TIME_STMP                                                                   USER_ID
--------------------------------------------------------------------------- ----------
PRG_NAME                       M MSG_ID
------------------------------ - ---------
MSG
------------------------------------------------------------------------------------------
06-02-11 15:16:46.219000                                                    NICEUSER
SAMPLE                         D TEST-0000
自律型トランザクションの確認

1 row selected.

SQL>

 ※この、自律型トランザクションは、トリガーでも使用できます。自律型トランザクショントリガーを作成して、 テーブルの行に対して行ったDML文を常時監視しているシステムも存在します。

 では、このログ出力の性能測定を実施してみます。次のPL/SQLブロックを五回実行して、それぞれ 処理時間を計測します。このスクリプトにより、10000件のログレコードが生成されます。処理時間の計測は、 SQL*PlusのSET TIMING ONを使用した、100分の1秒オーダーで測定することにします。 また、五回の計測条件ができるだけ同じになるように、毎回実行する前に、 ログ管理テーブルのデータをTRUNCATE TABLEしました。
計測スクリプト  ← こちらをクリック

DECLARE
    RET NUMBER := 0;  /* PLSQLLOGTEST_01_P.PLSQLLOGGINGの戻り値を格納 */
BEGIN
    /* 処理の開始メッセージをログ出力 */
    RET := PLSQLLOGTEST_01_P.PLSQLLOGGING(
                  CURRENT_TIMESTAMP
                , 'NICEUSER'
                , 'SAMPLE'
                , 'D'
                , 'TEST-0000'
                , '処理の開始'
            );
    /* 9998回ログ出力する */
    FOR i IN 1..9998 LOOP
        RET := PLSQLLOGTEST_01_P.PLSQLLOGGING(
                      CURRENT_TIMESTAMP
                    , 'NICEUSER'
                    , 'SAMPLE'
                    , 'D'
                    , 'TEST-0000'
                    , 'ループ ' || TO_CHAR(i) || '回目'
                );
    END LOOP;

    /* 無理矢理エラー */
    RAISE NO_DATA_FOUND;  /* データが見つかりません(ORA-01403)を発生 */
EXCEPTION
    WHEN OTHERS THEN
    RET := PLSQLLOGTEST_01_P.PLSQLLOGGING(
                  CURRENT_TIMESTAMP
                , 'NICEUSER'
                , 'SAMPLE'
                , 'D'
                , 'TEST-0000'
                , 'NO_DATA_FOUND'
            );
END;
/

 測定結果を以下にまとめました。

一回目 二回目 三回目 四回目 五回目 平均
00:00:02.25 00:00:02.34 00:00:02.28 00:00:02.25 00:00:02.31 00:00:02.286

 上記結果から、一行あたりのログ出力時間は「0.0002」と言うことで、ログ出力機能としては十分な 速度であると言えます。


 
ページのTOPへ

 DBMS_OUTPUTは、Oracleに組み込まれているパッケージの中でも、かなり有名な部類に あたります。使用方法が文字列引数を渡すだけと言う単純さもあり、PL/SQLの開発過程では良く使用されています。

 使用方法を確認してみましたので、以下に例を紹介します。SQL*Plusから実行する最初の1行目の命令は必須で、 SET SERVEROUTPUT ONを行わなければ、何も表示されません。 また、SET SERVEROUTPUT OFF を行うことで、非表示に戻すことができます。

 現在のSQL*Plusの設定がどちらになっているか確認するには SHOW SERVEROUTPUTを行います。  以下のスクリプトでは、ループ中で、何回目のループであるか、回数を表示しています。

SQL> SET SERVEROUTPUT ON
SQL> SHOW SERVEROUTPUT
serveroutput ON size 2000 format WORD_WRAPPED
SQL> DECLARE
  2  BEGIN
  3      FOR i IN 1..5 LOOP
  4          DBMS_OUTPUT.PUT_LINE('ループ ' || TO_CHAR(i) || '回目');
  5      END LOOP;
  6  END;
  7  /
ループ 1回目  --// ← SERVEROUTPUTがONなので、"ループ回数"が表示されます。
ループ 2回目
ループ 3回目
ループ 4回目
ループ 5回目

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT OFF
SQL> SHOW SERVEROUTPUT
serveroutput OFF
SQL> DECLARE
  2  BEGIN
  3      FOR i IN 1..5 LOOP
  4          DBMS_OUTPUT.PUT_LINE('ループ ' || TO_CHAR(i) || '回目');
  5      END LOOP;
  6  END;
  7  /
               --// ← SERVEROUTPUTがOFFなので、"ループ回数"が表示されません。
PL/SQL procedure successfully completed.

SQL>

 DBMS_OUTPUT.PUT_LINEプロシジャーは、PL/SQLから呼出された時点で、引数の文字列を専用のバッファに 書込みます。そして、PL/SQLの処理が終わった時点で、SERVEROUTPUTパラメータがONになっていれば、バッファの 内容を取出して表示するという仕組みで動きます。

 ですから、処理時間に長時間を要するPL/SQLブロックがどこまで進んだかを確認する目的で リアルタイム監視ログ的な役割 を担うことはできません(※処理が終わった時点で結果は表示されるので、PL/SQLブロックの所要時間 を確認する目的では使用できます。)。

 また、使用できるバッファに上限値が設けられています。使用できるバッファは2000バイト〜100000バイト までの範囲です。 SQL*Plusから SHOW SERVEROUTPUT と入力した場合に返される「serveroutput ON size 2000 format WORD_WRAPPED」 の「2000」は、DBMS_OUTPUTで使用できるバッファが最低値の「2000バイト」に設定されていることを表しています。

 SQL*Plusからバッファの使用量を変更するには、SET SERVEROUTPUT ON SIZE バイト数を入力します。 PL/SQLからも値を変更することができます。DBMS_OUTPUT.ENABLE(バイト数)をPL/SQLから実行することで変更できます。

 このDBMS_OUTPUTパッケージを使用して、ログ出力(ログレポート)してみます。 ログとして残す情報は、「§2) ログ出力時に出力する内容」であげたものをそのまま使用します。 出力する内容を半角スペースで文字連結して、一行で表示させたいと思います。イメージ的には、以下のような 出力結果を想定しています。


2006/02/06 14:15:06.638000000 USERNAME SAMPLE M TEST-0001 △△△処理開始。
2006/02/06 14:15:07.843000000 USERNAME SAMPLE E TEST-0001 ○○テーブルへの一意制約違反発生。
2006/02/06 14:15:08.165000000 USERNAME SAMPLE E TEST-0001 ○○テーブルへの登録処理に失敗。
2006/02/06 14:15:08.354000000 USERNAME SAMPLE E TEST-0001 ○○テーブルへの一意制約違反発生。
2006/02/06 14:15:08.989000000 USERNAME SAMPLE M TEST-0001 △△△処理終了。


 以下の無名PL/SQLブロックを用意し、これをSQL*Plusから実行します。 このスクリプトにより、10000件のログレポートが表示されるはずですが、バッファ使用量を 大きくはみ出し、ログレポートが尻切れとんぼになるはずです。想定内です。
試作スクリプト  ← こちらをクリック

DECLARE
BEGIN
    /* DBMS_OUTPUTパッケージが使用できるバッファを上限に上げる。 */
    DBMS_OUTPUT.ENABLE(100000);
   /* 処理の開始メッセージをログレポートとしてバッファに格納 → 半角スペース区切り形式 */
    DBMS_OUTPUT.PUT_LINE(
                  TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF')
                || ' ' || 'NICEUSER'
                || ' ' || 'SAMPLE'
                || ' ' || 'D'
                || ' ' || 'TEST-0000'
                || ' ' || '処理の開始'
            );
    /* 9998回ログレポートとしてバッファに格納する */
    FOR i IN 1..9998 LOOP
        DBMS_OUTPUT.PUT_LINE(
                      TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF')
                    || ' ' || 'NICEUSER'
                    || ' ' || 'SAMPLE'
                    || ' ' || 'D'
                    || ' ' || 'TEST-0000'
                    || ' ' || 'ループ ' || TO_CHAR(i) || '回目'
                );
    END LOOP;

    /* 無理矢理エラー */
    RAISE NO_DATA_FOUND;  /* データが見つかりません(ORA-01403)を発生 */
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(
                      TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF')
                    || ' ' || 'NICEUSER'
                    || ' ' || 'SAMPLE'
                    || ' ' || 'D'
                    || ' ' || 'TEST-0000'
                    || ' ' || 'NO_DATA_FOUND'
                );
END;
/

 実行結果、途中で「buffer overflow」が発生し処理が中断してしまいました。

SQL> @PLSQLLOGTEST_02_SCRIPT.sql

2006/02/12 22:10:41.843000000 NICEUSER SAMPLE D TEST-0000 処理の開始
2006/02/12 22:10:41.843000000 NICEUSER SAMPLE D TEST-0000 ループ 1回目
…
…
…
2006/02/12 22:10:41.953000000 NICEUSER SAMPLE D TEST-0000 ループ 1286回目
2006/02/12 22:10:41.953000000 NICEUSER SAMPLE D TEST-0000 ループ 1287回目
DECLARE
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 100000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 158
ORA-06512: at "SYS.DBMS_OUTPUT", line 121
ORA-06512: at line 30

SQL>

 計測用のスクリプト中に存在しているFOR LOOPのループ回数を10000回から1000回に変更すれば、計測用のスクリプト は最後までメッセージを出力してくれます。

 Oracleに組込みのパッケージを利用するだけなので、使いやすさは抜群です。利用する為の 設定方法も単純です。DBMS_OUTPUTパッケージ使用した際の処理速度をここでは計測しません。 テーブル書出し、ファイル出力とは違い、標準出力で処理速度が気になることはないでしょう。

 DBMS_OUTPUTパッケージの使用できるバッファの上限値が少ないことや、バッファに格納した結果をPL/SQLの 処理が終わるまで待たなければ確認できない点は不満が残ります。しかも、SERVEROUTPUTをONに設定し忘れでもしていたならば ストレスが溜まりそうです。
 
ページのTOPへ

 PL/SQLからファイルの入出力を実現することができるOracle組み込みパッケージにUTL_FILE があります。UTL_FILEパッケージを使用するために、事前準備としていくつかの作業が必要になります。

 必要な事前準備の方法には二種類あります。

 ■ 6-1) 初期化パラメータ"UTL_FILE_DIR"を設定して、UTL_FILEパッケージを使用する方法
 ■ 6-2) CREATE DIRECTORYを実行して、UTL_FILEパッケージを使用する方法  ※ Oracle9i以降で実施可能。

 どちらの設定であっても、UTL_FILEパッケージの使用方法に差が出るわけではないのですが、両方の環境設定を試してみたいと思います。


 ■ 6-1) 初期化パラメータ"UTL_FILE_DIR"を設定して、UTL_FILEパッケージを使用する方法

 UTL_FILE_DIRを設定してUTL_FILEパッケージを利用するには、次の手順で 環境を設定します。

 手順 1) Oracleサーバが読書き可能なディレクトリ/フォルダを作成する。

 Oracleサーバが読書き可能なディレクトリ/フォルダを作成します。今回の検証環境は Windows XP Professionalです。Cドライブに「C:\oracleWorkSpace\FileWriteDir」と言うフォルダを作成し、 ここをファイル出力先に指定することにしました。フォルダの属性が読書き可能になっていることを確認しています。

  

 手順 2) 初期化パラメータ"UTL_FILE_DIR"に 1) で作成したディレクトリ/フォルダを設定する。

 初期化パラメータ"UTL_FILE_DIR"に値を設定します。 パラメータを設定する前に、現在の設定値を確認します。 "UTL_FILE_DIR"の現在の設定値を確認するにはSHOW PARAMETERSコマンドをSQL*Plus から実行します。 確認した結果、VALUEの列に何も表示されませんでした。パラメータには何も設定されていない状態だったことがわかります。

SQL> SHOW PARAMETERS UTL_FILE_DIR
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string

SQL> 

 初期化パラメータ"UTL_FILE_DIR"を設定するには、ALTER SYSTEMコマンドを使用します(※Oracle8i以前では、初期化パラメータファイルを編集する必要がありました。)。
 ALTER SYSTEMコマンドを実行した直後に、SHOW PARAMETERSコマンドで設定値を確認してみました。しかし、 まだ設定値が反映されていないことがわかります。

SQL> CONNECT system/manager
Connected.
SQL> ALTER SYSTEM SET UTL_FILE_DIR='C:\oracleWorkSpace\FileWriteDir' SCOPE = SPFILE;

System altered.

SQL> SHOW PARAMETERS UTL_FILE_DIR
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string

SQL>

 手順 3) 上記初期化パラメータを有効にする為、Oracleを再起動する。

 次に、Oracleを再起動します。これによって、初期化パラメータ"UTL_FILE_DIR"に値が設定されます。

SQL> CONNECT system/manager as sysdba
Connected.
SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.
SQL> SHOW PARAMETERS UTL_FILE_DIR
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------------
utl_file_dir                         string      C:\oracleWorkSpace\FileWriteDir

SQL> 

 これで設定は完了です。

 今、初期化パラメータ"UTL_FILE_DIR"に設定したフォルダを一つ指定しましたが、複数指定する場合は、

ALTER SYSTEM SET UTL_FILE_DIR='C:\oracleWorkSpace\FileWriteDir','C:\FileWriteDir2' SCOPE = SPFILE;

のように、カンマでフォルダを区切って指定します。また、以下の様にワイルドカードによる指定も可能です。

ALTER SYSTEM SET UTL_FILE_DIR='C:\oracleWorkSpace\FileWriteDir/*' SCOPE = SPFILE;

しかし、このワイルドカード指定はセキュリティー上大変危険なことです。Oracleは、 初期化パラメータ"UTL_FILE_DIR"に指定されたフォルダに対する権限を全ユーザに適応してしまいます。 'C:\*'や、Oracleホームを指定してしまった場合などは、システムが完全無防備になっていると思って良いでしょう。 また、ワイルドカードを指定しない場合であっても、SHOW PARAMETERSコマンドで、簡単に 初期化パラメータに指定されたフォルダを閲覧できます。 ユーザが悪意を持ってそのフォルダにアクセスする危険性があります。

 次に、ログファイルを出力するパッケージを考えます。 ログとして残す情報は、「§2) ログ出力時に出力する内容」であげたものをそのまま使用します。 出力する内容を半角スペースで文字連結して、一行で表示させたいと思います。  CREATE PACKAGE文は、以下のようになります。
CREATE PACKAGE  ← こちらをクリック

/* -------------------------------- */
/* ログ出力パッケージ定義           */
/* -------------------------------- */
CREATE OR REPLACE PACKAGE PLSQLLOGTEST_03_P
AS
    /* ------------------------------------------ */
    /* ログ出力関数                               */
    /* 引 数:IP_TIME_STMP VARCHAR2              */
    /*       :IP_USER_ID VARCHAR2                */
    /*       :IP_PRG_NAME VARCHAR2               */
    /*       :IP_MSG_SECT VARCHAR2               */
    /*       :IP_MSG_ID VARCHAR2                 */
    /*       :IP_MSG VARCHAR2                    */
    /*       :IP_DIR_NAME VARCHAR2               */
    /*       :IP_FILE_NAME VARCHAR2              */
    /* 戻り値:正常終了  0                       */
    /*       :異常終了 -1                       */
    /* 説 明:UTL_FILEを使用したファイル書込み   */
    /* ------------------------------------------ */
    FUNCTION PLSQLLOGGING(
          IP_TIME_STMP TIMESTAMP
        , IP_USER_ID VARCHAR2 
        , IP_PRG_NAME VARCHAR2
        , IP_MSG_SECT VARCHAR2
        , IP_MSG_ID VARCHAR2
        , IP_MSG VARCHAR2
        , IP_DIR_NAME VARCHAR2
        , IP_FILE_NAME VARCHAR2
    ) RETURN NUMBER;

END PLSQLLOGTEST_03_P;
/
CREATE OR REPLACE PACKAGE BODY PLSQLLOGTEST_03_P
AS
    /* ------------------------------------------ */
    /* 変数・定数宣言                             */
    /* ------------------------------------------ */
    RETURN_NORMAL  CONSTANT NUMBER := 0; /* パッケージ内のFUNCTIONが正常終了した場合の戻り値 */
    RETURN_ERROR  CONSTANT NUMBER := -1; /* パッケージ内のFUNCTIONが異常終了した場合の戻り値 */
    FILE_HANDLE  UTL_FILE.FILE_TYPE;  /* FILEハンドラ */

    /* ------------------------------------------ */
    /* ログ出力関数                               */
    /* 引 数:IP_TIME_STMP VARCHAR2              */
    /*       :IP_USER_ID VARCHAR2                */
    /*       :IP_PRG_NAME VARCHAR2               */
    /*       :IP_MSG_SECT VARCHAR2               */
    /*       :IP_MSG_ID VARCHAR2                 */
    /*       :IP_MSG VARCHAR2                    */
    /*       :IP_DIR_NAME VARCHAR2               */
    /*       :IP_FILE_NAME VARCHAR2              */
    /* 戻り値:正常終了  0                       */
    /*       :異常終了 -1                       */
    /* 説 明:UTL_FILEを使用したファイル書込み   */
    /* ------------------------------------------ */
    FUNCTION PLSQLLOGGING(
          IP_TIME_STMP TIMESTAMP
        , IP_USER_ID VARCHAR2 
        , IP_PRG_NAME VARCHAR2
        , IP_MSG_SECT VARCHAR2
        , IP_MSG_ID VARCHAR2
        , IP_MSG VARCHAR2
        , IP_DIR_NAME VARCHAR2
        , IP_FILE_NAME VARCHAR2
    ) RETURN NUMBER AS
    BEGIN
        /* 出力先のファイルを追加書込みモードでOPEN */
        FILE_HANDLE := UTL_FILE.FOPEN(
                                  IP_DIR_NAME
                                , IP_FILE_NAME
                                , 'A'  /* "R"…Input、"W"…Output、"A"…Append */
                                , 32767  /* レコードバイト長:1 〜 32767 */
                        );

        /* 出力先のファイルへ書込み処理を行う。*/
        UTL_FILE.PUT_LINE(
                  FILE_HANDLE
                , IP_TIME_STMP
                || ' ' || IP_USER_ID
                || ' ' || IP_PRG_NAME
                || ' ' || IP_MSG_SECT
                || ' ' || IP_MSG_ID
                || ' ' || IP_MSG
            );

        /* 出力先のファイルをCLOSE */
	UTL_FILE.FCLOSE(FILE_HANDLE);

        RETURN RETURN_NORMAL;

    /* エラーは全て無視 */
    EXCEPTION
        WHEN OTHERS THEN
            IF UTL_FILE.IS_OPEN(FILE_HANDLE) = TRUE THEN
                UTL_FILE.FCLOSE(FILE_HANDLE);
            END IF;
            RETURN RETURN_ERROR;
    END PLSQLLOGGING;
END PLSQLLOGTEST_03_P;
/

 このログ出力の仕組みに対して、性能測定を実施してみます。次のPL/SQLブロックを五回実行して、それぞれ 処理時間を計測します。このスクリプトにより、10000件のログ出力が実施されます。

 ファイルの出力先フォルダは「C:\oracleWorkSpace\FileWriteDir」で、出力ファイル名は「PLSQLLOG.log」 にしています。処理時間の計測は、 SQL*PlusのSET TIMING ONを使用した、100分の1秒オーダーで測定することにします。 また、五回の計測条件ができるだけ同じになるように、毎回実行する前に、PLSQLLOG.logを削除しました。
計測スクリプト  ← こちらをクリック

DECLARE
    RET NUMBER := 0;  /* PLSQLLOGTEST_03_P.PLSQLLOGGINGの戻り値を格納 */
    DIR_NAME  VARCHAR2(100);  /* ディレクトリ */
    FILE_NAME  VARCHAR2(100);  /* ファイル名 */
BEGIN
    /* ファイル出力先のフォルダを指定する。 */
    DIR_NAME  := 'C:\oracleWorkSpace\FileWriteDir';
    /* ファイル出力先のファイル名を指定する。 */
    FILE_NAME := 'PLSQLLOG.log';
    /* 処理の開始メッセージをログ出力 */
    RET := PLSQLLOGTEST_03_P.PLSQLLOGGING(
                  TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF')
                , 'NICEUSER'
                , 'SAMPLE'
                , 'D'
                , 'TEST-0000'
                , '処理の開始'
                , DIR_NAME
                , FILE_NAME
            );
    /* 9998回ログ出力する */
    FOR i IN 1..9998 LOOP
        RET := PLSQLLOGTEST_03_P.PLSQLLOGGING(
                      TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF')
                    , 'NICEUSER'
                    , 'SAMPLE'
                    , 'D'
                    , 'TEST-0000'
                    , 'ループ ' || TO_CHAR(i) || '回目'
                    , DIR_NAME
                    , FILE_NAME
                );
    END LOOP;

    /* 無理矢理エラー */
    RAISE NO_DATA_FOUND;  /* データが見つかりません(ORA-01403)を発生 */

EXCEPTION
    WHEN OTHERS THEN
        RET := PLSQLLOGTEST_03_P.PLSQLLOGGING(
                      TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF')
                    , 'NICEUSER'
                    , 'SAMPLE'
                    , 'D'
                    , 'TEST-0000'
                    , 'NO_DATA_FOUND'
                    , DIR_NAME
                    , FILE_NAME
                );
END;
/

 測定結果を以下にまとめました。

一回目 二回目 三回目 四回目 五回目 平均
00:00:19.59 00:00:19.34 00:00:19.57 00:00:19.49 00:00:19.54 00:00:19.50.6

 上記結果から、一行あたりのログ出力時間は「0.0019」と言うことで、ログ出力機能としては十分な速度であると言えます。
実際に、「C:\oracleWorkSpace\FileWriteDir」に「PLSQLLOG.log」ファイルが作成されました。

  

  


 ■ 6-2) CREATE DIRECTORYを実行して、UTL_FILEパッケージを使用する方法

 CREATE DIRECTORYを設定してUTL_FILEパッケージを利用するには、次の手順で環境を設定します。

 手順 1) Oracleサーバが読書き可能なディレクトリ/フォルダを作成する。

 Oracleサーバが読書き可能なディレクトリ/フォルダを作成します。 6-1) で作成したフォルダと同じフォルダを使用します。

 手順 2) CREATE DIRECTORYを実行し、ディレクトリオブジェクトを作成する。

 CREATE DIRECTORYを実行して、ディレクトリオブジェクトを作成します。 ここでは、ディレクトリオブジェクトの名前を「PLSQLLOG_DIR」としました。設定を確認するには、 ALL_DIRECTORIESに問い合わせます。

SQL> CONNECT system/manager
Connected.
SQL>CREATE DIRECTORY PLSQLLOG_DIR AS 'C:\oracleWorkSpace\FileWriteDir';

Directory created.

SQL>SELECT DIRECTORY_PATH FROM ALL_DIRECTORIES WHERE DIRECTORY_NAME = 'PLSQLLOG_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------------
C:\oracleWorkSpace\FileWriteDir

1 row selected.

SQL> 

 手順 3) ディレクトリのアクセス権をユーザに付与する。

 ディレクトリのアクセス権をユーザに付与します。 与えるアクセス権は、ディレクトリへの書込み権限です。 下記の例では、ユーザー「NICE」にPLSQLLOG_DIRへの書込み権限を与えています。

SQL> CONNECT system/manager
Connected.
SQL> GRANT WRITE ON DIRECTORY PLSQLLOG_DIR TO NICE;

Grant succeeded.

SQL>

 これで設定は完了です。 「6-1) 初期化パラメータ"UTL_FILE_DIR"を設定して、UTL_FILEパッケージを使用する方法」との違いで、 CREATE DICTIONARYを使用する方法の一番の魅力は、Oracleの再起動が不要な点です。

 では、 6-1) で使用したものと同じスクリプトを使用して、同じ性能測定を実施してみます。測定結果を以下にまとめました。

一回目 二回目 三回目 四回目 五回目 平均
00:00:19.54 00:00:20.51 00:00:20.07 00:00:20.12 00:00:19.90 00:00:20.02.8

 6-1) に比べたら、若干速度が遅いようですが、気にするレベルではないようです。上記結果から、一行あたりのログ出力時間は「0.0020」と言うことで、ログ出力機能としては十分な速度であると言えます。

 ちなみに、ディレクトリオブジェクトの削除にはDROP DIRECTORYを実行します。

SQL> CONNECT system/manager
Connected.
SQL> DROP DIRECTORY PLSQLLOG_DIR;

Directory created.

SQL> SELECT DIRECTORY_PATH FROM ALL_DIRECTORIES WHERE DIRECTORY_NAME = 'PLSQLLOG_DIR';

no rows selected

SQL>


 これで、UTL_FILEパッケージを利用した実装方法の検証を終えますが、 ここまでの調査で、UTL_FILEパッケージが発するエラーに数回ぶつかりました。 これは、非常に使い辛いパッケージです。このパッケージを使用した経験 のある人ならば、皆さん同じように使い辛いと思っているはずです。 ファイル書出し先のフォルダの指定を誤るだけで、時によってOracleの再起動を 余儀なくされると言った事態に陥ることもあります。正しい使い方をすれば問題無いのですが、 経験の浅い開発者を抱える開発チームでは、経験豊富なリーダーがログ出力パッケージをエラーの起きない ガチガチのコードで作成するのが良いと思います。本番環境で、何度もOracle再起動を余儀なくされ、 その理由を報告する際に「ログ出力機能でエラーが発生して...」とは、言いにくいですよね?


 
ページのTOPへ

 Oracleでは、外部ルーチンコードとしてJavaのコードを利用することができます。 利用の仕方は簡単で、まず最初にJavaのコードを記載し、そのコードをデータベースにロードします。 この時点で、Javaはコンパイルされた状態でデータベースに格納されます。そして、そのJavaのメソッドを ラッパープロシジャー/ファンクションを介して呼出すだけです。

 データベースにJavaのコードをロードするには、 CREATE JAVA SOURCEコマンドを実行します。ロードするコードには NAMED句を用いて名前を付けます。ここでは、クラス名と同じ名前を 付けましたが、好きな名前を付けてかまいません。

 下記に、Javaで作成したログ出力クラスの例をあげます。文字色が緑色になっている部分が、 そっくりそのまま、Javaコードになっています。この緑色部分を***.javaとして抜き出し、 Eclipse等でデバッグや作り変えを行うことができます。

 ログとして残す情報は、 「§2) ログ出力時に出力する内容」 であげたものをそのまま使用します。出力する内容を半角スペースで文字連結して、一行で表示させたいと思います。
Javaログ出力クラス  ← こちらをクリック

CREATE OR REPLACE JAVA SOURCE NAMED PLSQLLoggingByJava
AS
import java.io.*;

public class PLSQLLoggingByJava {

    /**
     * ログ出力
     */
    public static int loggingByJava(
          String strTimeStamp
        , String strUserID
        , String strProgramName
        , String strMessageSector
        , String strMessageID
        , String strMessage
        , String strFileFullPath
    ) {
        // ログ出力時のセパレータ
        String sep = " ";
        // 正常終了時の戻り値
        int normalEnd = 0;   
        // 異常終了時の戻り値
        int abnormalEnd = -1;   

        try {
            // ログ出力する文字列を作成する。
            String logMessage = strTimeStamp + sep
                                + strUserID + sep
                                + strProgramName + sep
                                + strMessageSector + sep
                                + strMessageID + sep
                                + strMessage;

            // ログ出力ファイルのオープン
            PrintWriter pw 
                        = new PrintWriter ( new BufferedWriter(
                                    new FileWriter( strFileFullPath, true ) ));

            // ログ出力
            pw.println(logMessage);

            // ファイルクローズ
            pw.close();

            return normalEnd;

        } catch(Exception e) {
            return abnormalEnd;
        }
    }
}
/

 OracleにロードしたJavaのメソッドをPL/SQLから呼び出すには、 ラッパープロシジャー/ファンクションが必要になります。以下の様な、 パッケージを用意しました。
PLSQLラッパーパッケージ  ← こちらをクリック

 Javaの外部プロシジャーを呼び出すので、LANGUAGE句にjava を指定します。ラッピングするJavaのメソッドをNAME句で"クラス名"."メソッド名"で 指定します。これは、シングルクォートで囲む必要があります。

 PLSQLLOGGINGファンクションの引数は「VARCHAR2」になっていますが、Javaのメソッドには「java.lang.String」 としてマッピングします。PLSQLLOGGINGファンクションの戻り値は「NUMBER」になっていますが、 Javaのメソッドでは「int」にマッピングしています。「return」は小文字で記載します。

/* -------------------------------- */
/* ログ出力パッケージ定義           */
/* -------------------------------- */
CREATE OR REPLACE PACKAGE PLSQLLOGTEST_04_P
AS
    /* ------------------------------------------ */
    /* JavaLogラッパー関数                        */
    /* 引 数:IP_TIME_STMP VARCHAR2              */
    /*       :IP_USER_ID VARCHAR2                */
    /*       :IP_PRG_NAME VARCHAR2               */
    /*       :IP_MSG_SECT VARCHAR2               */
    /*       :IP_MSG_ID VARCHAR2                 */
    /*       :IP_MSG VARCHAR2                    */
    /*       :IP_FILE_FULLPATH_NAME VARCHAR2     */
    /* 戻り値:正常終了  0                       */
    /*       :異常終了 -1                       */
    /* 説 明:Javaソースを利用したファイル書込み */
    /* ------------------------------------------ */
    FUNCTION PLSQLLOGGING(
          IP_TIME_STMP VARCHAR2
        , IP_USER_ID VARCHAR2 
        , IP_PRG_NAME VARCHAR2
        , IP_MSG_SECT VARCHAR2
        , IP_MSG_ID VARCHAR2
        , IP_MSG VARCHAR2
        , IP_FILE_FULLPATH_NAME VARCHAR2
    ) RETURN NUMBER;

END PLSQLLOGTEST_04_P;
/
CREATE OR REPLACE PACKAGE BODY PLSQLLOGTEST_04_P
AS
    /* ------------------------------------------ */
    /* JavaLogラッパー関数                        */
    /* 引 数:IP_TIME_STMP VARCHAR2              */
    /*       :IP_USER_ID VARCHAR2                */
    /*       :IP_PRG_NAME VARCHAR2               */
    /*       :IP_MSG_SECT VARCHAR2               */
    /*       :IP_MSG_ID VARCHAR2                 */
    /*       :IP_MSG VARCHAR2                    */
    /*       :IP_FILE_FULLPATH_NAME VARCHAR2     */
    /* 戻り値:正常終了  0                       */
    /*       :異常終了 -1                       */
    /* 説 明:Javaソースを利用したファイル書込み */
    /* ------------------------------------------ */
    FUNCTION PLSQLLOGGING(
          IP_TIME_STMP VARCHAR2
        , IP_USER_ID VARCHAR2 
        , IP_PRG_NAME VARCHAR2
        , IP_MSG_SECT VARCHAR2
        , IP_MSG_ID VARCHAR2
        , IP_MSG VARCHAR2
        , IP_FILE_FULLPATH_NAME VARCHAR2
    ) RETURN NUMBER AS
    LANGUAGE java
    NAME 'PLSQLLoggingByJava.loggingByJava(
                      java.lang.String
                    , java.lang.String
                    , java.lang.String
                    , java.lang.String
                    , java.lang.String
                    , java.lang.String
                    , java.lang.String
                ) return int'	/* NAME句はシングルクォートで囲む必要があります。 */
    ;
END PLSQLLOGTEST_04_P;
/

 通常のJavaメソッドのコールでは、以上の作業だけで使用可能になりますが、今回は ログ出力機能によるファイルアクセス処理を含んでいるので、 Javaメソッドによってファイル書込みを許容するパーミションの設定が必要になります。

 Javaメソッドによってファイル書込みを許容するには、SYSTEMユーザになって、 DBMS_JAVA.GRANT_PERMISSIONプロシジャーを使用します。この時、指定するファイルの 格納先であるフォルダが存在していなくても、エラーになりません。しかし、事前にフォルダを作成しておく方が 良いと思います。  Oracleサーバが読書き可能なディレクトリ/フォルダを作成しておきます。今回の検証環境は Windows XP Professionalです。Cドライブに「C:\oracleWorkSpace\FileWriteDir」と言うフォルダを作成し、 ここをファイル出力先に指定することにしました。フォルダの属性が読書き可能になっていることを確認しています。

  

SQL> CONNECT system/manager
Connected.
SQL> CALL DBMS_JAVA.GRANT_PERMISSION(
   2       'NICE'                                           --// パーミションを与えるユーザ
   3     , 'SYS:java.io.FilePermission'                     --// パーミションの種類
   4     , 'C:\oracleWorkSpace\FileWriteDir\PLSQLLOG.log'   --// ファイルフルパス
   5     , 'write'                                          --// 書き込み権限
   6 );

Call completed.

SQL>

 このログ出力の仕組みに対して、性能測定を実施してみます。次のPL/SQLブロックを五回実行して、それぞれ 処理時間を計測します。このスクリプトにより、10000件のログ出力が実施されます。

 ファイルの出力先フォルダは「C:\oracleWorkSpace\FileWriteDir」で、出力ファイル名は「PLSQLLOG.log」 にしています。処理時間の計測は、 SQL*PlusのSET TIMING ONを使用した、100分の1秒オーダーで測定することにします。 また、五回の計測条件ができるだけ同じになるように、毎回実行する前に、PLSQLLOG.logを削除しました。
計測スクリプト  ← こちらをクリック

DECLARE
    RET NUMBER := 0;  /* PLSQLLOGTEST_04_P.PLSQLLOGGINGの戻り値を格納 */
    FILE_FULLPATH_NAME VARCHAR2(100);  /* ログ出力先のファイル名フルパス */
BEGIN
    /* ファイル出力先のファイル名をフルパスで指定する。 */
    FILE_FULLPATH_NAME := 'C:\oracleWorkSpace\FileWriteDir\PLSQLLOG.log';
    /* 処理の開始メッセージをログ出力 */
    RET := PLSQLLOGTEST_04_P.PLSQLLOGGING(
                  TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF')
                , 'NICEUSER'
                , 'SAMPLE'
                , 'D'
                , 'TEST-0000'
                , '処理の開始'
                , FILE_FULLPATH_NAME 
            );
    /* 9998回ログ出力する */
    FOR i IN 1..9998 LOOP
        RET := PLSQLLOGTEST_04_P.PLSQLLOGGING(
                      TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF')
                    , 'NICEUSER'
                    , 'SAMPLE'
                    , 'D'
                    , 'TEST-0000'
                    , 'ループ ' || TO_CHAR(i) || '回目'
                    , FILE_FULLPATH_NAME 
                );
    END LOOP;

    /* 無理矢理エラー */
    RAISE NO_DATA_FOUND;  /* データが見つかりません(ORA-01403)を発生 */

EXCEPTION
    WHEN OTHERS THEN
        RET := PLSQLLOGTEST_04_P.PLSQLLOGGING(
                      TO_CHAR(CURRENT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS.FF')
                    , 'NICEUSER'
                    , 'SAMPLE'
                    , 'D'
                    , 'TEST-0000'
                    , 'NO_DATA_FOUND'
                    , FILE_FULLPATH_NAME 
                );
    
END;
/

 測定結果を以下にまとめました。

一回目 二回目 三回目 四回目 五回目 平均
00:00:52.57 00:00:51.95 00:00:50.71 00:00:50.04 00:00:50.48 00:00:51.15

 上記結果から、一行あたりのログ出力時間は「0.0051」と言うことで、ログ出力機能としては十分な速度であると言えます。
実際に、「C:\oracleWorkSpace\FileWriteDir」に「PLSQLLOG.log」ファイルが作成されました。

  

  


 
ページのTOPへ
 ログをテーブル/ファイルに格納する場合、システムとしてログの保存期間を設定し、 定期的なバックアップ&データ削除を行う必要があります。格納先がテーブルの場合 、テーブルをパーティション分割し、日付単位でログを切捨て(TRUNCATE)する仕組み が求められると思います。ファイルの場合は、出力先となるファイル名を日付単位もしくはバイト単位で切り替 わる仕組みが求められると思います。
 DBMS_OUTPUTパッケージは、デバッグ時などにちょっとした標準出力機能とし て用いるのに便利であり、開発者にとって使用頻度が高い組込みパッケージです。しかし、 使用するにあたっての制限事項が多過ぎるため、このパッケージに満足している 開発者はそうそういないでしょう。
 PL/SQLからファイル出力する為に初期化パラメータ"UTL_FILE_DIR"を設定すると、 セキュリティー上の問題がおき、システムが危険にさらされます。 使用するOracleのバージョンが9i以降であれば、CREATE DIRECTORYコマンドを 用いた使用方法を採用しましょう。
 ログファイルを作成する方法として、UTL_FILEパッケージは、今回の検証では最良のパフォーマンスを見せました。 しかし、使い辛さも一級です。 制限事項も多いため、このパッケージの使い方に慣れるよりも、Javaの外部プロシジャー を使用した方法を進めます。外部プロシジャーを利用した方が、汎用性も向上すると思います。