pl/sqlのドキュメント生成

最近、pl/sqlのコードを書いてみたりしているが、ドキュメントの生成も欲しいところ、探すとPLDocというものがあったのでちょっと試した。

javadocと同じ感じで使用すると、ドキュメントをちゃんと生成してくれた。

この手のツールはあると非常に助かるが、pl/sqlのようなOracle社しかエンジン開発しないような言語は初めからOracle社として用意すべきではないだろうか？

いつかこの手のツールがOracleに標準装備されることを期待する。

 

pl/sqlからsyslogへ出力

ログ出力の種類として、syslogへの出力も欲しい。作成中のログ出力プログラムは出力する対象によりオブジェクトを用意するようになっているため、syslog出力用のオブジェクトも書いてみた。

------------------------------------
-- logger_syslog_typ
-- syslog出力型
------------------------------------
CREATE OR REPLACE TYPE logger_syslog_typ UNDER logger_typ
 (
 OVERRIDING MEMBER PROCEDURE write( MSG IN logmsg_typ )
 ) NOT final;
/

CREATE OR REPLACE TYPE BODY logger_syslog_typ AS
 OVERRIDING MEMBER PROCEDURE write( MSG IN logmsg_typ ) IS
     v_job_name varchar2(100);    --JOB NAME編集用
     v_action   varchar2(1000); --外部プログラム
 BEGIN
     v_job_name := 'SYSLOG' || to_char(systimestamp,'ff6');
     v_action := cb.GET_CONFIG_VALUE('SYS_LOG_PRG');

     DBMS_SCHEDULER.CREATE_JOB (
                                                   job_name =>v_job_name,
                                                   job_type =>'EXECUTABLE',
                                                   job_action =>v_action,
                                                   number_of_arguments => 1,
                                                   auto_drop => true
                                                   );

 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job_name,1, MSG.LOG_MSG);

 DBMS_SCHEDULER.ENABLE(v_job_name);

 EXCEPTION
      WHEN OTHERS THEN
               DBMS_OUTPUT.PUT_LINE(SQLCODE);
               DBMS_OUTPUT.PUT_LINE(SQLERRM);
 END;

そもそも、PLSQLからsyslogへ出力するには外部プログラム経由で出力しなければならない。（それしか方法を知らない）

外部プログラムに出力したい内容を渡して、そのプログラムがsyslogへ出力するという流れになる。外部プログラムはloggerを使ってもいいのだが、今回は自前perlで作成した。

#!/usr/bin/perl
use Sys::Syslog qw(:DEFAULT setlogsock);
setlogsock('unix');

openlog("ORACLE", 'ndelay', 'user');
syslog(LOG_ERR, $ARGV[0]);
closelog();

ほとんど、雑プログラムのレベルだ・・・。(loggerの方が良かったかも）

pl/sqlから外部プログラムを呼び出すにはcreate_jobすれば良い訳だが、ちゃんと権限を与えておかないとエラーになる。

• create any job システム権限
• create external job システム権限
• create job システム権限

これらをユーザへ与えておかないと実行時に権限が無いと怒られる。

CREATE_JOBに与えるjob_nameだが、本当は固定値で与えたいところだが、連続して呼び出された場合に、前のJOBが残っており、２回目以降のCREATE_JOBがエラーとなってしまう。そのため、timestampを使い、job_nameをユニークになるようにしている。出力先がsyslogの場合はやたらと出力せずに何か致命的なケースなどに出力する使い方を期待するとしよう。

追伸）やはり、syslogへの出力は他と比べて遅い。よって通常のログ出力オブジェクトと同列に扱うのはまずいので、syslogは特別に専用メソッドで扱うことにした方が良さそうである。

 

pl/sqlのログをファイルへも出力

前に作ったpl/sqlのログ出力オブジェクトにファイル出力タイプを追加してみる。

ログ出力は今まで、DBMS_OUTPUTを基本にそれを継承したTABLE書き込み型を用意してきたが、今回のファイル出力型も継承を使って実装する。

------------------------------------
-- logger_file_typ
-- ファイル出力型
------------------------------------
CREATE OR REPLACE TYPE logger_file_typ UNDER logger_typ
 (
     OVERRIDING MEMBER PROCEDURE write( MSG IN logmsg_typ )
 ) NOT final;
/

CREATE OR REPLACE TYPE BODY logger_file_typ AS
    OVERRIDING MEMBER PROCEDURE write( MSG IN logmsg_typ ) IS
        FH UTL_FILE.FILE_TYPE;    --ファイルハンドラ
        v_DIR  varchar2(1000);
        v_FILE varchar2(1000);
 BEGIN
        v_DIR :=  cb.GET_CONFIG_VALUE('APL_LOG_DIR');
        v_FILE := cb.GET_CONFIG_VALUE('APL_LOG_FILE');
        IF v_DIR is NULL or v_FILE is NULL THEN RETURN; END IF;

 -- ファイルOPEN
        FH := UTL_FILE.FOPEN( v_DIR,
                                        v_FILE,
                                        'A',
                                        32767);

        UTL_FILE.PUT_LINE(FH,
                                  MSG.LOG_DATE   || ' ' ||
                                  MSG.LOG_LEVEL  || ' ' ||
                                  MSG.LOG_MSG    || ' ' ||
                                  MSG.LOG_HOST   || ' ' ||
                                  MSG.LOG_OSUSER || ' ' ||
                                  MSG.LOG_PRG    || ' ' ||
                                  MSG.LOG_SUPPLE );
        UTL_FILE.FCLOSE(FH);

 EXCEPTION
 WHEN OTHERS THEN
       UTL_FILE.FCLOSE_ALL;
       DBMS_OUTPUT.PUT_LINE(SQLCODE);
       DBMS_OUTPUT.PUT_LINE(SQLERRM);
 END;

これでファイル出力型のオブジェクトは完成。プログラム側は変更すること無く出力先を切り替えることができる。

ディレクトリ名とファイル名の取得にcb.GET_CONFIG_VALUEを使っているが、これは自前のパッケージでコンフィグ情報から値を取り出すもの。

それにしても、OracleのUTL_FILEは面倒だ。以前のバージョンだと初期化パラメータファイルにUTL_FILE_DIRを指定して、FOPENでは許可されたディレクトリ名を記載する仕様だが、最近のはCREATE DIRECTORYで指定する。（UTL_FILE_DIRも使えるが。）その場合にはFOPENにはCREATE DIRECTORYで作成したディレクトリ名を指定しなければならない。勘違いして本当のディレクトリパスを書いてしまうと、ORA-29280: 無効なディレクトリ・パスです。と怒られる。これが紛らわしい。

pl/sqlのテスト

便利なパッケージとかを作るのはいいが、ちゃんとテストとかしなくちゃ使い物にならない。pl/sqlのユニットテストとかも無いわけではないが、簡単なものを作り、勉強しながら拡張させていった方が自分のタメになると思い。ちょっと書き始めた。

パッケージの仕様部はこんな感じ。

CREATE OR REPLACE PACKAGE tester IS
 /*===========================================================
 * Testerパッケージ
 *
 *==========================================================*/

     --テストタイトルをセット
     PROCEDURE SET_TEST( name IN varchar2 );
     --テストの実行
     PROCEDURE DO_TEST( msg IN varchar2, test IN BOOLEAN);
     --テスト結果表示
     PROCEDURE GET_RESULT;
 END tester;
 

最初は簡単にこんなもんからスタートでいいかと実装。

SET_TESTはテストの名前と初期化を行う。

-----------------------
 -- テストの設定
 ----------------------  
 PROCEDURE SET_TEST( name IN varchar2 ) IS
 BEGIN
     test_name := substrb(name, 1, 100);
     ArrayTest.DELETE;
 END;

ArrayTestというのは個別のテストの結果を格納するためのコレクション。

--======================================================--   
 test_name varchar2(100);

 TYPE test_rec_typ IS RECORD (
     t_comment   varchar2(100),
     t_status    boolean
 );
 TYPE ArrayTestTyp IS TABLE OF test_rec_typ;
 ArrayTest ArrayTestTyp;         --test内容格納配列
 --=======================================================--

SET_TESTでテスト名をセットしたら、後はDO_TESTを使ってテストしていく。DO_TESTはこんな感じ。

----------------------
 -- DO_TEST
 ----------------------
 PROCEDURE DO_TEST( msg IN varchar2, test IN BOOLEAN) IS
     one_test test_rec_typ;
 BEGIN
     -- テストレコードの作成
     one_test.t_comment := substrb(msg, 1, 100);
     one_test.t_status := NVL(test,FALSE);
     -- テスト配列へ追加
     ArrayTest.EXTEND;
     ArrayTest( ArrayTest.LAST ) := one_test;
 END;

テストしたい内容をDO_TESTへ投げたら、最後にGET_RESULTする。

-----------------------
 -- 結果の取得
 -----------------------
 PROCEDURE GET_RESULT IS
     ng_count NUMBER(5);
 BEGIN
     ng_count :=0;

     DBMS_OUTPUT.PUT_LINE( test_name );
     FOR i IN 1 .. ArrayTest.COUNT LOOP
         DBMS_OUTPUT.PUT( chr(9) || i || '. ');
         DBMS_OUTPUT.PUT( ArrayTest(i).t_comment );
         DBMS_OUTPUT.PUT( chr(9) || '..........' );
         IF ArrayTest(i).t_status = TRUE THEN
             DBMS_OUTPUT.PUT_LINE( 'ok' );
         ELSE
             DBMS_OUTPUT.PUT_LINE( 'ng' );
             ng_count := ng_count + 1;
         END IF;
     END LOOP;

     IF ng_count = 0 THEN
         DBMS_OUTPUT.PUT_LINE( '*** Total ' || ArrayTest.COUNT || ' Test Clear');     
     ELSE
         DBMS_OUTPUT.PUT_LINE( '*** Total ' || ng_count || '/' || ArrayTest.COUNT || ' Test Failed !!');
         RAISE_APPLICATION_ERROR(-20000,'Tester STOP !!');     
     END IF;
 END;

ちょっと機能的にも簡単過ぎるかもしれない。

実行するためのテストスクリプトを書く。

DECLARE

BEGIN
     tester.set_test('Util package Test');

     -- 文字列split test
     utl.split('aaa bbb ccc',' ');
     tester.do_test('空白デリミタ',utl.get_split_value(3) = 'ccc');

     utl.split('123.5    bbb         ccc',' ');
     tester.do_test('連続するスペース区切り １',utl.get_split_value(1) = '123.5');
     tester.do_test('連続するスペース区切り ２',utl.get_split_value(2) = 'bbb');
     tester.do_test('連続するスペース区切り ３',utl.get_split_value(3) = 'ccc');
     tester.do_test('存在しない要素番号指定',utl.get_split_value(4) is NULL);
     tester.do_test('split結果の要素数取得',utl.get_split_count = 3);

     tester.get_result;
END;
/

sqlplus でset serveroutput onしてこのスクリプトを実行してみる。

SQL> @test_util
Util package Test
 1. 空白デリミタ ..........ok
 2. 連続するスペース区切り １    ..........ok
 3. 連続するスペース区切り ２    ..........ok
 4. 連続するスペース区切り ３    ..........ok
 5. 存在しない要素番号指定       ..........ok
 6. split結果の要素数取得        ..........ok
*** Total 6 Test Clear

PL/SQLプロシージャが正常に完了しました。

SQL>

機能的には不十分だが、スタートとしてはこんなところから始めよう。これでも無いよりはマシ。UnitTestの他の実装を見て必要機能を勉強しなくては・・・

pl/sqlの例外処理をスマートに書きたい

ログ出力のための仕組みを簡単に作成したので、今度は例外が発生したときの試行錯誤。
例外を取り扱うのも、いちいちルールを決めて個別のコードでたくさん記載するのは面倒だし、TYPEとpackageで何とか簡単にできないかなということで記載してみた。

例外の情報を格納するためのTYPEを作る。

/*----------------------------
 TYPE宣言
-----------------------------*/
--例外基本オブジェクト
CREATE OR REPLACE TYPE excep_typ AS OBJECT
 (
      EXP_CODE     number(5),
      EXP_MSG      varchar2(2048),
      EXP_PRG      varchar2(50),
      EXP_ERRSTACK varchar2(2000)
 ) NOT final;
/

エラー発生のコードとメッセージ、エラー発生場所、後はあればスタック情報を保持する。

これを生成したりするpackageはこんな感じ。

CREATE OR REPLACE PACKAGE BODY excep IS

 PROCEDURE e_catch IS
     my_excep excep_typ;
 BEGIN
     --excepオブジェクトを作成し、ログへ記録する
     my_excep := excep_typ(NULL,NULL,NULL,NULL);
     my_excep.EXP_CODE := SQLCODE;
     my_excep.EXP_MSG  := substrb(SQLERRM, 1,2048);     
     --例外の発生したモジュール名を取得
     my_excep.EXP_PRG  := substrb(utl.get_caller_name, 1,50);
     my_excep.EXP_ERRSTACK := substrb(dbms_utility.format_error_stack, 1,2000);

     logger.write( my_excep );                 --Oracleの例外はログ記録させた方がいいだろう。

   EXCEPTION
     WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE(SQLERRM);
 END;
 

e_cacheというプロシジャでオブジェクトを生成し例外のコードやらメッセージを取得してセットしている。また、この時に自動的にログ出力されるようにloggerへexceptionオブジェクトを渡している。

プログラムからはexceptionのところに以下のように記載。

EXCEPTION
    WHEN OTHERS THEN
             excep.e_catch;

この一行で、例外のコードを取得し、ログへ自動的に記録する。後はお好みでやればいい。