PL/SQL のエラーハンドリング

エラー発生方法

PL/SQL 実行中に、参照する表を更新することで ORA-1555 を発生させエラーとする

ORA-1555 の再現ケース - tomy_125: Note

EXCEPTION なし

15:18:15 SQL> set serveroutput on
15:21:15 SQL> declare
15:21:15   2    vC1 number(10);
15:21:15   3    vC2 varchar(500);
15:21:15   4  begin
15:21:15   5      select
15:21:15   6        /*+ LEADING(a b) USE_NL(b) */
15:21:15   7        a.c1, a.c2
15:21:15   8        into vC1, vC2
15:21:15   9      from t1 a, t1 b
15:21:15  10      order by a.c1, b.c1
15:21:15  11      ;
15:21:15  12  end;
15:21:15  13  /
declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 9 with name
"_SYSSMU9_3581032004$" too small
ORA-06512: at line 5


Elapsed: 00:00:09.24
15:21:26 SQL>

EXCEPTION あり、OTHER でハンドリング

15:21:26 SQL> set serveroutput on
15:22:09 SQL> declare
15:22:09   2    vC1 number(10);
15:22:09   3    vC2 varchar(500);
15:22:09   4  begin
15:22:09   5      select
15:22:09   6        /*+ LEADING(a b) USE_NL(b) */
15:22:09   7        a.c1, a.c2
15:22:09   8        into vC1, vC2
15:22:09   9      from t1 a, t1 b
15:22:09  10      order by a.c1, b.c1
15:22:09  11      ;
15:22:09  12  exception
15:22:09  13      when others then
15:22:09  14          dbms_output.put_line('-- other error received.');
15:22:09  15  end;
15:22:09  16  /
-- other error received.

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.67
15:22:19 SQL>

EXCEPTION あり、ユーザー定義例外

15:23:00 SQL> set serveroutput on
15:23:01 SQL> declare
15:23:01   2    vC1 number(10);
15:23:01   3    vC2 varchar(500);
15:23:01   4    snapshot_too_old exception;
15:23:01   5    pragma exception_init(snapshot_too_old, -1555);
15:23:01   6  begin
15:23:01   7      select
15:23:01   8        /*+ LEADING(a b) USE_NL(b) */
15:23:01   9        a.c1, a.c2
15:23:01  10        into vC1, vC2
15:23:01  11      from t1 a, t1 b
15:23:01  12      order by a.c1, b.c1
15:23:01  13      ;
15:23:01  14  exception
15:23:01  15      when snapshot_too_old then
15:23:01  16          dbms_output.put_line('-- ORA-1555 received.');
15:23:01  17      when others then
15:23:01  18          dbms_output.put_line('-- other error received.');
15:23:01  19  end;
15:23:01  20  /
-- ORA-1555 received.

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.77
15:23:13 SQL>