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

2009年 11月 1日

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

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

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

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

 

広告

pl/sqlからsyslogへ出力

2009年 10月 30日

ログ出力の種類として、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が残っており、2回目以降のCREATE_JOBがエラーとなってしまう。そのため、timestampを使い、job_nameをユニークになるようにしている。出力先がsyslogの場合はやたらと出力せずに何か致命的なケースなどに出力する使い方を期待するとしよう。

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

 


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

2009年 10月 30日

前に作った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のテスト

2009年 10月 26日

便利なパッケージとかを作るのはいいが、ちゃんとテストとかしなくちゃ使い物にならない。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('連続するスペース区切り 1',utl.get_split_value(1) = '123.5');
     tester.do_test('連続するスペース区切り 2',utl.get_split_value(2) = 'bbb');
     tester.do_test('連続するスペース区切り 3',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. 連続するスペース区切り 1    ..........ok
 3. 連続するスペース区切り 2    ..........ok
 4. 連続するスペース区切り 3    ..........ok
 5. 存在しない要素番号指定       ..........ok
 6. split結果の要素数取得        ..........ok
*** Total 6 Test Clear

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

SQL>

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


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

2009年 10月 24日

ログ出力のための仕組みを簡単に作成したので、今度は例外が発生したときの試行錯誤。
例外を取り扱うのも、いちいちルールを決めて個別のコードでたくさん記載するのは面倒だし、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;

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


OracleのPL/SQLで継承を使ってみた

2009年 10月 17日

OracleのPL/SQLで無理やりに継承を使ってみる。簡単な例でログ出力の仕組みでも作ってみた。

PL/SQLで継承を使うにはTYPEを作成してオブジェクト?を作成する。

--ログ出力用基本オブジェクト
CREATE OR REPLACE TYPE logger_typ AS OBJECT
 (
      MY_TYPE      varchar2(20),
      MEMBER PROCEDURE write( MSG IN logmsg_typ )
 ) NOT final;
/
--基本形 (DBMS_OUTPUT)
CREATE OR REPLACE TYPE BODY logger_typ AS
    MEMBER PROCEDURE write( MSG IN logmsg_typ ) IS
    BEGIN
       DBMS_OUTPUT.PUT_LINE(MSG.LOG_DATE   || ' ' ||
       MSG.LOG_LEVEL  || ' ' ||
       MSG.LOG_MSG    || ' ' ||
       MSG.LOG_HOST   || ' ' ||
       MSG.LOG_OSUSER || ' ' ||
       MSG.LOG_PRG    || ' ' ||
       MSG.LOG_SUPPLE );
    END;
END;
/

ログ出力用のオブジェクトなので、もっとも基本の形をDBMS_OUTPUTを使用したものにした。TYPEの中でwriteプロシジャを持たせている。

writeプロシジャへの引数としては出力するメッセージを格納したTYPEを渡している。

CREATE OR REPLACE TYPE logmsg_typ AS OBJECT
 (
 LOG_DATE     TIMESTAMP,
 LOG_LEVEL    varchar2(3),
 LOG_MSG      varchar2(1000),
 LOG_HOST     varchar2(30),
 LOG_OSUSER   varchar2(30),
 LOG_PRG      varchar2(50),
 LOG_SUPPLE   varchar2(2000)
 ) NOT final;
/

さて、DBMS_OUTPUT以外にテーブルへも格納したくなったので、基本形のオブジェクトを継承してwriteプロシジャをオーバーライドする。

-- logger_table_typ
-- テーブル出力型 (自律型トランザクションを使用)
CREATE OR REPLACE TYPE logger_table_typ UNDER logger_typ
 (
      OVERRIDING MEMBER PROCEDURE write( MSG IN logmsg_typ )
 ) NOT final;
/

CREATE OR REPLACE TYPE BODY logger_table_typ AS
     OVERRIDING MEMBER PROCEDURE write( MSG IN logmsg_typ ) IS
     PRAGMA AUTONOMOUS_TRANSACTION;
     BEGIN
        INSERT INTO APL_LOG_TABLE (
                   L_DATE,
                   L_LEVEL,
                   L_MSG,
                   L_HOST,
                   L_OSUSER,
                   L_PRG,
                   L_SUPPLE
        ) values (
                   MSG.LOG_DATE,
                   MSG.LOG_LEVEL,
                   MSG.LOG_MSG,
                   MSG.LOG_HOST,
                   MSG.LOG_OSUSER,
                   MSG.LOG_PRG,
                   MSG.LOG_SUPPLE
        );
       COMMIT;
 EXCEPTION
    WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE(SQLCODE);
             DBMS_OUTPUT.PUT_LINE(SQLERRM);
 END;
END;

使う側としては同じように呼び出しをするが、生成するオブジェクトをlogger_typにするかlogger_table_typにするかで出力先が画面出力かテーブル出力か切り替わる。

ちなみに、テーブル出力はログ記録を確実に残したいので自律型トランザクションを宣言して、呼び出し側のrollbackに関係なく記録されるようにするのは常識だ。

こんな感じで、ファイル出力型もあっていいかもしれない。これはこれで継承してwriteプロシジャの中身を変えてしまえばいい。

しかし、単純にこれだけでは使う方も面倒だ。例えば複数の記録媒体へログを記録したいときなどプログラムが複数のオブジェクトを直接操作することになる。そういうことを避けるためpackageを用意する。

CREATE OR REPLACE PACKAGE logger IS
/*===========================================================
 * plsqlログ出力用パッケージ
 *==========================================================*/
 --デフォルトのログ出力先
 DEFAULT_LOGGER varchar2(50) := 'DBMS_OUTPUT';

 --ログ出力オブジェクトの追加
 --引数なしで呼ばれる場合はDEFAULT_LOGGERがセットされる。
 PROCEDURE SET_LOGGER;
 PROCEDURE SET_LOGGER( logger_type IN varchar2 );
 --ログ出力オブジェクトの削除
 PROCEDURE DEL_LOGGER( logger_type IN varchar2 );

 --ログオブジェクトの数を取得
 FUNCTION GET_LOGGER_COUNT RETURN NUMBER;

 --ログ出力用
 PROCEDURE write( msg IN varchar2 );    
 PROCEDURE write( log_level IN varchar2,
 msg IN varchar2 );
 PROCEDURE write( err IN excep_typ );

END logger;
/

まずはシンプルに、このくらいの機能で始めてみる。(こえは仕様部)

SET_LOGGERは指定されたログ記録オブジェクトを用意するプロシジャ。これは複数のオブジェクトを保持できるように配列にして保持する。

--ログ出力オブジェクト格納エリア(複数のオブジェクトを格納可能とする)
 TYPE ArrayLoggerTyp IS TABLE OF logger_typ;
 ArrayLogger ArrayLoggerTyp;

ただし外部から直接操作させないために、packageヘッダー部ではなくbodyの方へ書く。

SET_LOGGERはこんな感じ。

----------------
 -- ログ出力オブジェクトの生成(指定あり)
 ----------------
 PROCEDURE SET_LOGGER( logger_type IN varchar2 ) IS
     my_logger logger_typ := NULL;
 BEGIN

 IF ArrayLogger.COUNT > 0 THEN
 --------------------
 -- 生成されるloggerが既に登録済みかチェック
 --------------------
     FOR i IN ArrayLogger.FIRST .. ArrayLogger.LAST
     LOOP
        IF ( ArrayLogger(i).MY_TYPE =  logger_type ) THEN
            RETURN;
        END IF;
     END LOOP;
 END IF;

 CASE upper(logger_type)
     WHEN 'DBMS_OUTPUT' THEN
        ArrayLogger.EXTEND;
        ArrayLogger(ArrayLogger.LAST) := logger_typ('DBMS_OUTPUT');
     WHEN 'TABLE' THEN
        ArrayLogger.EXTEND;
        ArrayLogger(ArrayLogger.LAST) := logger_table_typ('TABLE');
     WHEN 'FILE' THEN
        NULL;
     ELSE
        NULL;
 END CASE;

 EXCEPTION
     WHEN OTHERS THEN
        NULL;        
 END;

少し美しくない部分もあるが、まずはお試しということで良とする。オブジェクトのコンストラクタでは引数でどのタイプのログ出力オブジェクトかを表す文字列を渡している。これは同じタイプのオブジェクトを複数登録された場合にログ出力内容が重複してしまうのを防ぐためで、SET_LOGGERの最初に登録済みのオブジェクトと重複していないかを必ずチェックする。

DEL_LOGGERは指定されたログ記録オブジェクトを削除する。削除された時から対象のログ記録はされなくなる。

GET_LOGGER_COUNTはログ記録オブジェクトの数を知るため。(通常はあまり使用しないだろう)

writeプロシジャはいろいろな引数でログを記録するためのプロシジャ。単純に文字列を渡された場合や、ログレベルと文字列を渡された場合、例外が発生したときに例外オブジェクトを渡され記録する場合など。

ちなみに、例外オブジェクトというのも自前で作成する。これは例外処理用のpackageを別途用意しており、そこでオブジェクトが生成される。

テスト用のコードを書いてみる。

DECLARE
BEGIN
 -- デフォルトログ出力はDBMS_OUTPUTを使った画面出力
 logger.write('This is logger test!!');
 logger.write('E','This is logger ERROR test!!');
 DBMS_OUTPUT.PUT_LINE(logger.GET_LOGGER_COUNT);

 -- 重複したログ出力オブジェクトを指定しても問題ないか?
 logger.SET_LOGGER('DBMS_OUTPUT');
 logger.write('This is logger 2 test!!');
 logger.write('E','This is logger ERROR 2 test!!');
 DBMS_OUTPUT.PUT_LINE(logger.GET_LOGGER_COUNT);

 -- テーブル出力ログオブジェクトを追加
 -- この時ログ出力オブジェクトは2種類。DBMS_OUTPUTとTABLE
 -- 1回のwriteで両方に出力される。
 logger.SET_LOGGER('TABLE');
 logger.write('This is logger table test!!');
 logger.write('E','This is logger ERROR table test!!');
 DBMS_OUTPUT.PUT_LINE(logger.GET_LOGGER_COUNT);

 -- DBMS_OUTPUTログオブジェクトを削除
 -- この時点からログ出力はTABLEのみになる。
 logger.DEL_LOGGER('DBMS_OUTPUT');
 logger.write('This is logger table only test!!');
 logger.write('E','This is logger ERROR table only test!!');
 DBMS_OUTPUT.PUT_LINE(logger.GET_LOGGER_COUNT);


END;

お試しの感じでPL/SQLの継承機能を使ってみたが、やはり本格的なオブジェクト指向言語に比べものにはならない。そもそもそう言う場合はjavaを使えと言うことかもしれないが・・・


Tora

2009年 10月 4日

Oracleを使用した開発といえば、随分と昔からツールとして使用してきたのがToraである。Linux環境を開発環境ベースとして使用してきても、やはりこの手のツールは欲しい。

最近はあまり使う機会が無かったのだが、久しぶりに使ってみることにした。うれしいことに日々開発は続いていたようで、新しいバージョンも出ている。Debianパッケージでも揃っていればいいのだが、ちょうどいいのが無かったのでダウンロードしてソースからビルドしてみた。

snap8なかなかいい感じである。昔に比べて機能も増えたようだ。

snap9今時は当たり前でもあるが、日本語だってちゃんと通る。が、日本語を入力するとインラインで表示されなくて確定しないと解らないのはイケてない。(設定の問題か?)

機能的にも豊富だし、昔から開発されてきているツールということもあって信頼もある。(昔は良く落ちてたが。)LinuxでOracle開発していてこのツールはかなり重宝すること間違いなしだろう。