SQLCODE, SQLERRM을 이용한 예외정보 참조

ch10_exception_proc 프로시저는 0으로 나눈 예외가 발생했을 때 단순히 메시지를 출력하기만 했는데, 개발자 입장에서는 실제로 어떤 오류가 발생했는지 알고 싶을 것이다. 이를 위해 오라클에서는 발생한 예외 정보를 참조하기 위한 수단으로 SQLCODE와 SQLERRM 이란 빌트인 함수를 제공하고 있다.

SQLCODE는 실행부에서 발생한 예외에 해당하는 코드를 반환한다. 예를 들어, 0으로 나누면 이에 대한 예외 코드인 ‘-1476’을 반환한다. 만약 에러 없이 성공하면 ‘0’을 반환한다.

SQLERRM은 발생한 예외에 대한 오류 메시지를 반환한다. 이 함수는 매개변수로 예외코드 값을 받는데, 매개변수를 넘기지 않으면 디폴트로 SQLCODE가 반환한 예외코드 값과 연관된 예외 메시지를 반환한다. 실제로 어떤 식으로 사용하는지 예제를 통해 알아 보자.

입력

    CREATE OR REPLACE PROCEDURE ch10_exception_proc
    IS
      vi_num NUMBER := 0;
    BEGIN
      vi_num := 10 / 0;
      DBMS_OUTPUT.PUT_LINE('Success!');

    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('오류가 발생했습니다');
      DBMS_OUTPUT.PUT_LINE('SQL ERROR CODE:' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE:' || SQLERRM); -- 매개변수 없는 SQLERRM
      DBMS_OUTPUT.PUT_LINE( SQLERRM(SQLCODE)); -- 매개변수 있는 SQLERRM

    END;

결과

    PROCEDURE CH10_EXCEPTION_PROC이(가) 컴파일되었습니다.

SQLCODE는 매개변수가 없으나 SQLERRM은 매개변수가 없는 형태와 매개변수로 예외코드를 전달해 사용할 수 있다. 이 프로시저를 실행해 보자.

입력

    EXEC ch10_exception_proc;

결과

    오류가 발생했습니다.
    SQL ERROR CODE: -1476
    SQL ERROR MESSAGE: ORA-01476: 제수가 0입니다.
    ORA-01476: 제수가 0 입니다.

결과를 보면 SQLCODE는 “-1476”을, SQLERRM은 “ORA-01476: 제수가 0입니다”를 반환했다. 예외 코드를 별도로 사용하지 않고 SQLERRM 함수만 사용하더라도 예외 코드와 메시지를 모두 확인할 수 있다.

이 두 함수 외에도 오라클에서 제공하는 시스템 패키지 중 하나인 DBMS_UTILITY를 사용해서 예외 정보를 좀더 세부적으로 볼 수 있다. 이 패키지에 속하는 서브 프로그램 중 DBMS_UTILITY. FORAMT_ERROR_BACKTRACE 함수를 사용하면 몇 번째 줄에서 예외가 발생했는지도 알 수 있다.

입력

    CREATE OR REPLACE PROCEDURE ch10_exception_proc
    IS
      vi_num NUMBER := 0;
    BEGIN
      vi_num := 10 / 0;
      DBMS_OUTPUT.PUT_LINE('Success!');

    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('오류가 발생했습니다');
      DBMS_OUTPUT.PUT_LINE('SQL ERROR CODE: ' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);  -- 매개변수 없는 SQLERRM
      DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    END;

결과

    PROCEDURE CH10_EXCEPTION_PROC이(가) 컴파일되었습니다.

입력

    EXEC ch10_exception_proc;

결과

    오류가 발생했습니다.
    SQL ERROR CODE: -1476
    SQL ERROR MESSAGE: ORA-01476: 제수가 0입니다.
    ORA-06512: "ORA_USER.CH12_EXCEPTION_PROC", 5행

출력 내용 중 맨 마지막 줄을 보면 소스코드의 5행에서 예외가 발생했다. 이 외에도 DBMS_UTILLITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_CALL_STACK을 사용하면 발생한 예외에 대해 좀더 자세한 정보를 알 수 있다.