아래의 procedure를 실행시키면  server output 이  아래 두개만 나옵니다. 

procedure에 문제가 있는건지요~  

고수님들 문제가 뭔지 부탁드립니다....



 DBMS_OUTPUT.PUT_LINE( '데이터 삭제 성공 ' );
DBMS_OUTPUT.PUT_LINE('SP_TM_DN_LOG PROCEDURE INSERT TIME : ' || v_sys_date);




CREATE OR REPLACE PROCEDURE TMS21.SP_TM_DN_LOG
IS

v_sys_date varchar2(20);
v_exists_check integer;
v_error_check integer;

BEGIN

    DBMS_OUTPUT.ENABLE ;

    SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') INTO v_sys_date FROM DUAL;
   
    DBMS_OUTPUT.PUT_LINE('SP_TM_DN_LOG PROCEDURE START TIME : ' || v_sys_date);

    v_error_check := 0;

    DELETE FROM TB_CAMPAIGN_CUSTOMER_DN
    WHERE START_TIME BETWEEN TO_DATE(TO_CHAR(SYSDATE-1, 'YYYY/MM/DD') || '00:00:00', 'YYYY/MM/DD HH24:MI:SS')
   AND TO_DATE(TO_CHAR(SYSDATE-1, 'YYYY/MM/DD') || '23:59:59', 'YYYY/MM/DD HH24:MI:SS');

    --COMMIT;
    DBMS_OUTPUT.PUT_LINE( '데이터 삭제 성공 ' );
   

    SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') INTO v_sys_date FROM DUAL;
   
    DBMS_OUTPUT.PUT_LINE('SP_TM_DN_LOG PROCEDURE INSERT TIME : ' || v_sys_date);

    INSERT INTO TB_CAMPAIGN_CUSTOMER_DN (
    DPS_NO
    , C_SEQ
    , CAMPAIGN_SEQ
    , DN_TM
    , DN_DM
    , DN_SMS
    , START_TIME
    , DPS_CK_DT
    )
    SELECT
    DPS_NO
    , C.C_SEQ
    , C.CAMPAIGN_SEQ
    , DN_TM
    , DN_DM
    , DN_SMS
    , start_time
    , SYSDATE
    FROM TMS21.TB_CAMPAIGN_CUSTOMER C
    INNER JOIN (SELECT DISTINCT C_SEQ, CAMPAIGN_SEQ, START_TIME
    FROM TB_CAMPAIGN_CUSTOMER_LOG
    WHERE START_TIME
    BETWEEN TO_DATE(TO_CHAR(SYSDATE-1, 'YYYY/MM/DD') || '00:00:00', 'YYYY/MM/DD HH24:MI:SS')
    AND TO_DATE(TO_CHAR(SYSDATE-1, 'YYYY/MM/DD') || '23:59:59', 'YYYY/MM/DD HH24:MI:SS')
    GROUP BY C_SEQ, CAMPAIGN_SEQ, START_TIME ) l
    ON C.C_SEQ = L.C_SEQ AND C.CAMPAIGN_SEQ = L.CAMPAIGN_SEQ
    WHERE 1=1
    AND (DPS_NO IS NOT NULL OR DPS_NO <> '')
    AND (DN_TM='Y' OR DN_DM='Y' OR DN_SMS='Y')
    ORDER BY 1, 2,3;

    EXCEPTION
    WHEN  DUP_VAL_ON_INDEX  THEN
    DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX : ㅣLINE 중복 발생...ROLLBACK');
    v_error_check := 1;
    ROLLBACK;
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND  : DID NOT INSERT ....');
    v_error_check := 2;
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ETC ERROR :  ...ROLLBACK' );
    v_error_check :=1;

    COMMIT;
   
   
    SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') INTO v_sys_date FROM DUAL;
   
    DBMS_OUTPUT.PUT_LINE('SP_TM_DN_LOG PROCEDURE END TIME : ' || v_sys_date);
   
    IF v_error_check = 0 THEN
    DBMS_OUTPUT.PUT_LINE('PROCEDURE SUCCESS....COMMIT');
    ELSE
    DBMS_OUTPUT.PUT_LINE('PROCEDURE FAIL.......ROLLBACK');
    END IF;

END SP_TM_DN_LOG;