예외처리 EXCEPTION 예제

CREATE OR REPLACE PROCEDURE ch10_exception_proc

IS

    vi_num NUMBER := 0;

BEGIN

    vi_num :=10 / 0;

    DBMS_OUTPUT.PUT_LINE('SUCCESS!');   H

    EXCEPTION WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('오류발생했어요');   A

    DBMS_OUTPUT.PUT_LINE('SQL ERROR CODE:'|| SQLCODE);   B

    DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE' ||SQLERRM);  C

    DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));  D

    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);   E  -- 몇번째행 오류인지 알수있음

    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);  F

    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);   G

END;

/

실행문:

DECLARE

BEGIN

    ch10_exception_proc;

    DBMS_OUTPUT.PUT_LINE('SUCCESS');

END;

/

결과값:

A  오류발생했어요

B  SQL ERROR CODE:-1476

C  SQL ERROR MESSAGEORA-01476: 제수가 0 입니다

D  ORA-01476: 제수가 0 입니다

E  ORA-06512: "HR.CH10_EXCEPTION_PROC",  5행

F  ORA-01476: 제수가 0 입니다

G

----- PL/SQL Call Stack -----

  object      line  object

  handle    number  name

000007FF58E46550        14  procedure HR.CH10_EXCEPTION_PROC

000007FF582CE040         4  anonymous block

H SUCCESS