ちょいと迷惑だったストアドファンクション（おまけ） Tweet

す〜〜ぱ〜〜し〜〜し〜〜でぃ〜〜っ。はにかむ、せぶん！
やけに耳の残っちゃうんだよな〜。あの声とアクセント。

なんて関係ない話はおいといて。。と


以前、
ちょいと迷惑だったストアドファンクション
ちょいと迷惑だったストアドファンクション（つづき）

ということを書いていたが、ちょいと迷惑だったストアドファンクションの例外ハンドラーで問題解決の手がかりとするためログ出力が実装されていたらどうするの？　っていうこともあるのでおまけのおまけということで書いておきます。

以前の例では例外ハンドラーをバッサリ切り捨ててしまったのですが、例外ハンドラーで問題解決の手がかりとするためのログ出力が実装されていて例外ハンドラーを取り除くことが出来ない場合、例外名無しのRAISE文を使えば解決できますよ。:)

ちょいと迷惑だったストアドファンクションの例外ハンドラーで問題解決の手がかりとするためのログ出力を実装していたらという例・・・・（赤太字部分）

尚、この例ではオープンソースのPL/SQL向けロギングフレームワークLog4PLSQLを利用している。

CREATE OR REPLACE
PACKAGE inconvenient_package
AS
  TYPE  employeesType IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
  TYPE  membersType IS RECORD (
    deptNo    dept.deptNo%TYPE
   ,deptName  dept.dName%TYPE
   ,employees employeesType
  );
  FUNCTION inconvenient_function(iDeptNo IN dept.deptNo%TYPE)
    RETURN membersType;
END inconvenient_package;
/
show errors


CREATE OR REPLACE
PACKAGE BODY inconvenient_package
AS
  FUNCTION inconvenient_function(iDeptNo IN dept.deptNo%TYPE)
  RETURN membersType
  AS
    members         membersType;
    Log4PlsqlCtx    plogparam.log_ctx := plog.init(pDBMS_OUTPUT => TRUE); -- Log4PLSQL setting
  BEGIN
    SELECT
      deptNo
     ,dName
    INTO
      members.deptNo
     ,members.deptName
    FROM
      dept
    WHERE
      deptNo = iDeptNo
    ;
    --
    SELECT *
    BULK COLLECT INTO
      members.employees
    FROM
      emp
    WHERE
      deptno = iDeptNo
    ;
    RETURN members;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      plog.warn(Log4PlsqlCtx, sqlerrm());  -- Log4PLSQL warnning
      RETURN NULL;
  END inconvenient_function;
END inconvenient_package;
/
show errors

変更前ファンクションの例外ハンドラー部分にある"RETURN NULL;"を"RAISE;"（例外名無しのRAISE文）に変更するだけ。

CREATE OR REPLACE
PACKAGE BODY inconvenient_package
AS
  FUNCTION inconvenient_function(iDeptNo IN dept.deptNo%TYPE)
  RETURN membersType
  AS
    members         membersType;
    Log4PlsqlCtx    plogparam.log_ctx := plog.init(pDBMS_OUTPUT => TRUE); -- Log4PLSQL setting
  BEGIN
    SELECT
      deptNo
     ,dName
    INTO
      members.deptNo
     ,members.deptName
    FROM
      dept
    WHERE
      deptNo = iDeptNo
    ;
    --
    SELECT *
    BULK COLLECT INTO
      members.employees
    FROM
      emp
    WHERE
      deptno = iDeptNo
    ;
    RETURN members;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      plog.warn(Log4PlsqlCtx, sqlerrm());  -- Log4PLSQL warnning
      RAISE;
  END inconvenient_function;
END inconvenient_package;
/

例外ハンドラー内で例外名を省略すると該当する例外がさらに外側のブロックへスローされます。詳細はマニュアル（Oracle Database PL/SQL Language Reference 11g Release 1 : RIASE statement）及び、Oracle Database PL/SQL Language Reference 11g Release 1 : How PL/SQL Exceptions Propagate参照のこと。

SCOTT> set linesize 132
SCOTT> set serveroutput on
SCOTT> l
  1  DECLARE
  2    members inconvenient_package.membersType;
  3    Log4PlsqlCtx plogparam.log_ctx := plog.init(pDBMS_OUTPUT => TRUE);
  4  BEGIN
  5    BEGIN
  6      members := inconvenient_package.inconvenient_function(99);
  7      DBMS_OUTPUT.PUT_LINE(
  8        'Dept name/id:'
  9        ||members.deptname
 10        ||'/'||TO_CHAR(members.deptno)
 11      );
 12      FOR i IN members.employees.FIRST..members.employees.LAST LOOP
 13        DBMS_OUTPUT.PUT_LINE(' -- '||members.employees(i).ename);
 14      END LOOP;
 15    EXCEPTION
 16      WHEN NO_DATA_FOUND THEN
 17        plog.warn(Log4PlsqlCtx, sqlerrm());
 18    END;
 19* END;
SCOTT> /
12:35:27:02-WARN-block-->SCOTT.INCONVENIENT_PACKAGE  ORA-01403: データが見つかりません。
12:35:27:03-WARN-block  ORA-01403: データが見つかりません。

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

SCOTT> l
  1  DECLARE
  2    members inconvenient_package.membersType;
  3    Log4PlsqlCtx plogparam.log_ctx := plog.init(pDBMS_OUTPUT => TRUE);
  4  BEGIN
  5    BEGIN
  6      members := inconvenient_package.inconvenient_function(10);
  7      DBMS_OUTPUT.PUT_LINE(
  8        'Dept name/id:'
  9        ||members.deptname
 10        ||'/'||TO_CHAR(members.deptno)
 11      );
 12      FOR i IN members.employees.FIRST..members.employees.LAST LOOP
 13        DBMS_OUTPUT.PUT_LINE(' -- '||members.employees(i).ename);
 14      END LOOP;
 15    EXCEPTION
 16      WHEN NO_DATA_FOUND THEN
 17        plog.warn(Log4PlsqlCtx, sqlerrm());
 18    END;
 19* END;
SCOTT> /
Dept name/id:ACCOUNTING/10
-- CLARK
-- KING
-- MILLER

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

SCOTT>