PL/SQL에서 사용자 예외 처리

사용자 예외 처리시 RAISE_APPLICATION_ERROR 구문을 사용하는데, 예외 상황이 발생할 경우 ORA-06512 메시지가 같이 나오더군요. 구루비 커뮤니티에서 사용자가 정의한 오류만 출력하는 방법에 대한 질문글이 있어서 나중에 참고도 할겸 찾아보았습니다.

 

먼저 구루비 커뮤니티 강좌의 사용자 정의 예외 예제입니다. (www.gurubee.net/lecture/1073)

CREATE OR REPLACE PROCEDURE User_Exception 
        (v_deptno IN emp.deptno%type ) 
     IS

       -- 예외의 이름을 선언
       user_define_error EXCEPTION;     -- STEP 1
       cnt     NUMBER;

     BEGIN

       DBMS_OUTPUT.ENABLE;   

       SELECT COUNT(empno) 
       INTO cnt
       FROM emp
       WHERE deptno = v_deptno;

       IF cnt < 5 THEN
         -- RAISE문을 사용하여 직접적으로 예외를 발생시킨다
          RAISE user_define_error;         -- STEP 2
       END IF;

      EXCEPTION
        -- 예외가 발생할 경우 해당 예외를 참조한다.
       WHEN user_define_error THEN      -- STEP 3
           RAISE_APPLICATION_ERROR(-20001, '부서에 사원이 몇명 안되네요..');

   END; 
  /

위의 프로시저를 실행하면 아래와 같이 사용자가 정의한 오류메시지와 함께 ORA-06512 메시지도 같이 출력됩니다.

SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE User_Exception(10);
BEGIN User_Exception(10); END;

*
ERROR at line 1:
ORA-20001: 부서에 사원이 몇명 안되네요.. ORA-06512:
at "SCOTT.USER_EXCEPTION", line 26
ORA-06512: at line 1

 

오라클 매뉴얼과 구글로 검색해보니 EXCEPTION_INIT이란 구문이 보입니다. RAISE_APPLICATION_ERROR와 조합하여 사용할 수 있군요. 구루비 커뮤니티에도 설명이 있습니다.

stackoverflow.com/questions/6020450/oracle-pl-sql-raise-user-defined-exception-with-custom-sqlerrm

www.gurubee.net/lecture/1072

 

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    ex_custom EXCEPTION;
  3    PRAGMA EXCEPTION_INIT( ex_custom, -20001 );
  4  begin
  5    raise_application_error( -20001, 'This is a custom error' );
  6  exception
  7    when ex_custom
  8    then
  9      dbms_output.put_line( sqlerrm );
 10* end;
SQL> /
ORA-20001: This is a custom error

PL/SQL procedure successfully completed.

 

이것을 응용해 원래 코드를 살짝 고쳐보았습니다.

CREATE OR REPLACE PROCEDURE User_Exception 
        (v_deptno IN emp.deptno%type ) 
     IS

       -- 예외의 이름을 선언
       user_define_error EXCEPTION;     -- STEP 1
       cnt     NUMBER;
       PRAGMA EXCEPTION_INIT(user_define_error, -20001);

     BEGIN

       DBMS_OUTPUT.ENABLE;   

       SELECT COUNT(empno) 
       INTO cnt
       FROM emp
       WHERE deptno = v_deptno;

       IF cnt < 5 THEN
         -- RAISE문을 사용하여 직접적으로 예외를 발생시킨다
          RAISE_APPLICATION_ERROR(-20001, '부서에 사원이 몇명 안되네요..');
       END IF;

      EXCEPTION
        -- 예외가 발생할 경우 해당 예외를 참조한다.
       WHEN user_define_error THEN      -- STEP 3
           DBMS_OUTPUT.PUT_LINE( sqlerrm );

   END; 
  /

아까와 같이 실행해보니 ORA-06512 메시지는 출력되지 않았습니다.

SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE User_Exception(10);
ORA-20001: 부서에 사원이 몇명 안되네요..

PL/SQL procedure successfully completed.