SQL Developer で ORA-XXXX が発生したらブレークさせる

2021年10月4日

『PL/SQL で作ったプロシジャが ORA-XXXX で落ちたんだけど原因となったデータがどれかわからない～』なんて経験ありませんか。しかも膨大なコード書いてたりすると原因以前にどこで落ちたかもわからない～なんてこともありますよね。「そりゃ例外処理をちゃんとやってないからでしょ、、、」なんて元も子もないこと言わないで、今回は SQL Developer の「例外ブレークポイント」を使って、ORA-XXXX が発生したときにブレークさせて原因となるデータの特定しましょう。

1. データ準備
2. デバッグの準備
3. デバッグ実行

データ準備

デバッグするプロシジャは「SAMPLE1 テーブルのレコード全件を取得し、特に加工せず SAMPLE2 テーブル挿入する」という仕様にします。まずは、入力となる SAMPLE1 テーブルを作成します。
以後、すべてのオブジェクトのスキーマは「SCOTT」で実施します。

CREATE TABLE SAMPLE1 ( 
  COL1  NUMBER(18),
  COL2  NVARCHAR2(50),
  COL3  CHAR(10)
) ;

次に出力となる SAMPLE2 テーブルです。同じ構造ですが、こちらはCOL1 にプライマリーキー制約をつけます。

CREATE TABLE SAMPLE2 ( 
  COL1  NUMBER(18)    NOT NULL,
  COL2  NVARCHAR2(50),
  COL3  CHAR(10), 
  CONSTRAINT PK_SAMPLE1 PRIMARY KEY(COL1) 
) ;

次にテストデータです。前述の仕様だと、COL1=3 の２件目で一意制約違反（ORA-0001）が発生するはずですね。

DELETE FROM SAMPLE1;
INSERT INTO SAMPLE1 VALUES ( 1, 'TEST1',     'A' );
INSERT INTO SAMPLE1 VALUES ( 2, 'TEST2',     'B' );
INSERT INTO SAMPLE1 VALUES ( 3, 'TEST3',     'C' );
INSERT INTO SAMPLE1 VALUES ( 3, '!!TEST4!!', 'D' );
INSERT INTO SAMPLE1 VALUES ( 5, 'TEST5',     'E' );
COMMIT;

最後にプロシジャです。冒頭の仕様を簡単に実装します。

CREATE OR REPLACE PROCEDURE TESTPROC
IS
  oRec SAMPLE2%ROWTYPE;
BEGIN
  FOR iRec IN (SELECT * FROM SAMPLE1)
  LOOP
    oRec.COL1 := iRec.COL1;
    oRec.COL2 := iRec.COL2;
    oRec.COL3 := iRec.COL3;
    
    INSERT INTO SAMPLE2 VALUES oRec;
  END LOOP;
END
;

念のため、直接実行してエラーで終了するかを確認します。

正しく（？）一意制約違反（ORA-00001）で落ちましたね。これでデータ準備はできました。

デバッグの準備

デバッグ用にコンパイル

ではデバッグを行うための準備を行います。まず SQL Developer で今回テストするテスト用ユーザ SCOTT でログインして「プロシージャ」配下で作成したテスト用のプロシジャを選択し、「デバッグ用にコンパイル」し直します。これを忘れてるとデバッガで停止しませんのでご注意を。

デバッグ用にコンパイルすると、プロシジャのアイコンが以下のように変わります。

デバッグ権限を追加する

次に今回テストで利用するユーザ SCOTT にデバッグを行う権限を与えます。これを与えずにデバッグ実行すると次のエラーになります。

PL/SQLの実行中: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', '56324' )
ORA-01031: 権限が不足しています
ORA-06512: "SYS.DBMS_DEBUG_JDWP", 行68
ORA-06512: 行1
このセッションでは、DEBUG CONNECT SESSIONおよびDEBUG ANY PROCEDUREユーザー権限が必要です。
プロセスが終了しました。

ということで権限を付与できるユーザでログインした後に以下の通り SCOTT に権限を与えます。

GRANT DEBUG CONNECT SESSION,DEBUG ANY PROCEDURE TO SCOTT;

アクセス許可を与える

いつのバージョンからか外部からのアクセスには厳しくなっていますので、DBMS_NETWORK_ACL_ADMIN パッケージを使って、外部からデバッガで接続できる権限を与えます。これもサボると次のエラーになります。

PL/SQLの実行中: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', '62042' )
ORA-24247: アクセス制御リスト(ACL)によりネットワーク・アクセスが拒否されました
ORA-06512: "SYS.DBMS_DEBUG_JDWP", 行68
ORA-06512: 行1
プロセスが終了しました。

今回は Oracle は自PCに同居した状態なので「127.0.0.1」からのアクセスだけを許可するようにします。ちなみにすべてのアクセスを許可する場合は「*」、一部のアドレスで許可する場合は「192.168.0.*」といった書き方になります。

BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
(
    host => '127.0.0.1',
    ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),
    principal_name => 'SCOTT', 
    principal_type => xs_acl.ptype_db)
);
END;

ちなみに設定したACLについては以下で確認できます。

SELECT * FROM DBA_HOST_ACES;

これでデバッグの準備は完了です。

デバッグ実行

ソースブレークポイントでのデバッグ

とりあえずは普通に「ソースの〇行目で停止」といった「ソース・ブレークポイント」を設定してデバッグ実行してみます。以下のような流れです。

左ペインでプロシージャ「TESTPROC」を選択
表示されたコード上にブレークポイントを設定（画像はFOR文の左をクリックしています）
デバッグ実行ボタンをクリック（てんとう虫ボタン）
デバッグ実行ダイアログ上で必要な編集を実施（パラメータの指定等。今回はパラメータ不要なので何もしません。）
OKボタンをクリック

次のように指定したブレークポイントで停止すれば成功です。

例外ブレークポイントでのデバッグ

本題の「例外ブレークポイント」の設定です。紛らわしいので前述のソースブレークポイントは削除しておいてください。例外ブレークポイントはソースに対して設定するものではないので、最初に「ブレークポイント」ウィンドウを表示します。

次にブレークポイントウィンドウから「＋」ボタン→「Exception Breakpoint」を選択します。

例外クラスのところで「$Oracle.EXCEPTION_ORA_00001」と記載します。今回は一意制約違反(ORA-00001)を捉えるのでこうなりますが、例えば「ORA-01403 データが見つかりません」を捕捉したいなら「$Oracle.EXCEPTION_ORA_01403」と設定します。