[Oracle] 프로시저 예외처리 이렇게 하자!

PROCEDURE MLOSIDRDRW_INS01
(
    IN_FACTORY       IN    VARCHAR2,   -- 공장
    IN_IDR_NO        IN    VARCHAR2,   -- IDR_NO
    IN_ITEM          IN    NUMBER,     -- ITEM
    IN_DRW_LOC       IN    VARCHAR2,   --
    IN_PAGE          IN    VARCHAR2,   --
    IN_QTY           IN    NUMBER,     --
    IN_REQ_SPEC      IN    VARCHAR2,   --
    IN_RESULT        IN    VARCHAR2,   --
    IN_DESCRIPT      IN    VARCHAR2,   -- 비고
    IN_USER_ID       IN    VARCHAR2,
    OUT_CUR          OUT   T_CURSOR
)
AS
   V_SEQ_NO          MLOSIDRDRW.SEQ_NO%TYPE;
BEGIN
   BEGIN

      ACTIVE_YN := 'N';

      IF IN_IDR_NO = '' THEN
         ACTIVE_YN := 'N';
         RAISE_MSG := 'IDR 번호가 누락되었습니다.';
         RAISE RAISE_EXT;
      END IF;
    
     BEGIN
        SELECT NVL((MAX(SEQ_NO) + 1), 4001)
          INTO V_SEQ_NO
          FROM MLOSIDRDRW
         WHERE IDR_NO = IN_IDR_NO;
     EXCEPTION WHEN OTHERS THEN
         V_SEQ_NO := 4001;    
     END;

      INSERT INTO MLOSIDRDRW (  IDR_NO
                              , SEQ_NO
                              , FACTORY
                              , ITEM
                              , DRW_LOC
                              , PAGE
                              , QTY
                              , REQ_SPEC
                              , RESULT
                              , DESCRIPT
                              , CRTUSER
                              , CRTTIME )
                      VALUES (
                                IN_IDR_NO
                              , V_SEQ_NO
                              , IN_FACTORY
                              , IN_ITEM
                              , IN_DRW_LOC  
                              , IN_PAGE
                              , IN_QTY
                              , IN_REQ_SPEC 
                              , IN_RESULT   
                              , IN_DESCRIPT 
                              , IN_USER_ID
                              , SYSDATE );

      ACTIVE_YN := 'Y';
      RAISE_MSG := '정상적으로 등록되었습니다.';

   EXCEPTION
       WHEN RAISE_EXT        THEN ROLLBACK;
           ACTIVE_YN := 'N';
           DELETE FROM MLOSIDRDAT WHERE IDR_NO = IN_IDR_NO;
           DELETE FROM MLOSIDRDRW WHERE IDR_NO = IN_IDR_NO;
       WHEN NO_DATA_FOUND    THEN ROLLBACK;
           ACTIVE_YN := 'N';
           RAISE_MSG := 'EXCEPTION - NO_DATA_FOUND IDRDRW';
           DELETE FROM MLOSIDRDAT WHERE IDR_NO = IN_IDR_NO;
           DELETE FROM MLOSIDRDRW WHERE IDR_NO = IN_IDR_NO;
       WHEN OTHERS           THEN ROLLBACK;
           ACTIVE_YN := 'N';
           RAISE_MSG := SUBSTR(SQLERRM, 11, 200);
           --RAISE_MSG := 'EXCEPTION - OTHERS IDRDRW';
           DELETE FROM MLOSIDRDAT WHERE IDR_NO = IN_IDR_NO;
           DELETE FROM MLOSIDRDRW WHERE IDR_NO = IN_IDR_NO;
   END;

   OPEN  OUT_CUR FOR
   SELECT ACTIVE_YN       AS ACTIVE_YN
        , RAISE_MSG       AS RAISE_MSG
   FROM DUAL;

END MLOSIDRDRW_INS01;