ODI - Operator Log 삭제 Procedure

-- Operator 로그 삭제하는 Procedure

CREATE OR REPLACE PROCEDURE ODI_DELETE_LOG_BYDATE
/*** 
Author : WideTNS Co.
Usage  : exec ODI_DELETE_LOG_BYDATE('20100101123000');
Producted date : 2010.05.25
***/
(
    p_date in VARCHAR2
    /***
    Type   : VARCHAR2
       Format : 'YYYYMMDDHH24MISS'
       Description :
     - criteria for judgement to delete Operator Log
        - delete logs executed before
  ***/
 )
IS
    v_sessNo NUMBER;
BEGIN
    DBMS_OUTPUT.ENABLE;
   
    SELECT MAX(SESS_NO)
        INTO v_sessNo
    FROM SNP_SESSION
    WHERE SESS_END <=
        to_date(p_date,'yyyymmddhh24miss');
   
--    DBMS_OUTPUT.PUT_LINE('criteria session No : '||v_sessNo);
   
    DELETE FROM SNP_SESS_TXT_LOG WHERE SESS_NO <= v_sessNo;
   
--    DBMS_OUTPUT.PUT_LINE('delete snp_sess_txt_log  count : '||SQL%ROWCOUNT);
   
    DELETE FROM SNP_SESS_TASK_LOG WHERE SESS_NO <= v_sessNo;   
--    DBMS_OUTPUT.PUT_LINE('delete snp_sess_task_log count : '||SQL%ROWCOUNT);
   
    DELETE FROM SNP_STEP_LOG WHERE SESS_NO <= v_sessNo;   
--    DBMS_OUTPUT.PUT_LINE('delete snp_step_log      count : '||SQL%ROWCOUNT);
   
    DELETE FROM SNP_TASK_TXT WHERE SESS_NO <= v_sessNo;   
--    DBMS_OUTPUT.PUT_LINE('delete snp_task_txt      count : '||SQL%ROWCOUNT);
   
    DELETE FROM SNP_SESS_TASK WHERE SESS_NO <= v_sessNo;   
--    DBMS_OUTPUT.PUT_LINE('delete snp_sess_task     count : '||SQL%ROWCOUNT);
   
    DELETE FROM SNP_SESS_STEP WHERE SESS_NO <= v_sessNo;   
--    DBMS_OUTPUT.PUT_LINE('delete snp_sess_step     count : '||SQL%ROWCOUNT);
   
    DELETE FROM SNP_VAR_SESS WHERE SESS_NO <= v_sessNo;   
--    DBMS_OUTPUT.PUT_LINE('delete snp_var_sess      count : '||SQL%ROWCOUNT);
   
    DELETE FROM SNP_SEQ_SESS WHERE SESS_NO <= v_sessNo;   
--    DBMS_OUTPUT.PUT_LINE('delete snp_seq_sess      count : '||SQL%ROWCOUNT);
   
    DELETE FROM SNP_SESSION WHERE SESS_NO <= v_sessNo;   
    DBMS_OUTPUT.PUT_LINE('delete snp_session         count : '||SQL%ROWCOUNT);
   
 COMMIT;
end;   
/