ストアドプロシージャのエラー調査方法（Oracle）

Oracleのストアドプロシージャで例外が発生した時の調査方法の備忘録です。

ストアドプロシージャを作成

サンプルのストアドプロシージャを作成します。
このストアドプロシージャは引数に１以外の値を渡したら例外が発生するようにします。

CREATE OR REPLACE PROCEDURE PROCEDURE_SAMPLE (p IN  NUMBER) IS
  r NUMBER;
BEGIN
  SELECT 100 INTO r FROM DUAL WHERE 1 = p;
  DBMS_OUTPUT.PUT_LINE('結果:' || r);
END;

こんな風に引数に２を渡して実行するとエラーになります。

EXEC PROCEDURE_SAMPLE(2);

次のコマンドの開始中にエラーが発生しました : 行 1 -
EXEC PROCEDURE_SAMPLE(2)
エラー・レポート -
ORA-01403: データが見つかりません。
ORA-06512: "TESTDB.PROCEDURE_SAMPLE", 行4
ORA-06512: 行1
01403. 00000 -  "no data found"
*Cause:    
*Action:

例外処理を追加

ストアドプロシージャにEXCEPTION文を追加して例外が発生したらキャッチするようにします。
この中でRAISE_APPLICATION_ERRORを使って独自の例外に引数などの情報を含めると出力結果で確認することができます。

CREATE OR REPLACE PROCEDURE PROCEDURE_SAMPLE (p IN  NUMBER) IS
  r NUMBER;
BEGIN
  SELECT 100 INTO r FROM DUAL WHERE 1 = p;
  DBMS_OUTPUT.PUT_LINE('結果:' || r);
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20000, 'ERRPR ' || SQLCODE || ': ' || SQLERRM || ' 引数:' || p);
--    DBMS_OUTPUT.PUT_LINE('ERRPR ' || SQLCODE || ': ' || SQLERRM);
--    DBMS_OUTPUT.PUT_LINE('引数:' || p);
--    RAISE;
END;

エラーコードは「-20000」から「-20999」の範囲で指定できます。

これを実行してみると、引数が２ということが確認できました。

次のコマンドの開始中にエラーが発生しました : 行 1 -
EXEC PROCEDURE_SAMPLE(2)
エラー・レポート -
ORA-20000: ERRPR 100: ORA-01403: データが見つかりません。 引数:2
ORA-06512: "TESTDB.PROCEDURE_SAMPLE", 行8
ORA-06512: 行1
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.