1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108
| TEXT
--------------------------------------------------------------------------------
PROCEDURE PURGE_EAILOGGER (
indiceCommit in integer, nbLineOut out integer, statutCode out varchar2)
IS
flowcodeC VARCHAR2(64 BYTE);
logidC NUMBER;
contextidC VARCHAR2(64 BYTE);
flowidC VARCHAR2(256 BYTE);
indice number ;
nbLine number ;
TEXT
--------------------------------------------------------------------------------
file_handle UTL_FILE.FILE_TYPE;
v_file_name VARCHAR2(256) := 'log_purge_eailogger_'|| TO_CHAR(sysdate, 'YYYY
MMDD_HH24MI') || '.txt';
v_file_location VARCHAR2(256) := 'PURGE_EAILOGGER_REP';
Cursor DataToPurge IS
select DISTINCT l.flowcode, l.logid , l.contextid , l.flowid
from log_event l ,dalkia_gui.halfflow_description tf
where cast(l.clientlogtimestamp as date) < sysdate-tf.retentionday
and tf.halfflow_code =l.halfflowcode
TEXT
--------------------------------------------------------------------------------
and tf.ispurge='Y' and halfflowcode!='FLAT_FILE-TO-FLAT_FILE'
--group by l.flowcode,l.halfflowcode,l.flowid
order by l.flowcode desc;
begin
file_handle := UTL_FILE.FOPEN(v_file_location,v_file_name,'A');
UTL_FILE.PUT_LINE(file_handle,'Purge begins at '|| TO_CHAR(sysdate, 'YYYY/MM
/DD_HH24:MI'));
TEXT
--------------------------------------------------------------------------------
UTL_FILE.FFLUSH(file_handle);
indice:=0;
nbLine:=0;
statutCode:='OK';
nbLineOut:=0;
Open DataToPurge;
Loop
Fetch DataToPurge into flowcodeC,logidC,contextidC,FLOWIDC;
exit when DataToPurge%NOTFOUND;
TEXT
--------------------------------------------------------------------------------
begin
delete from exception e where e.logid=logidC;
delete from msg_body m where m.contextid=contextidC;
delete from process_context p where p.contextid=contextidC;
delete from application_context ac where ac.flowid=FLOWIDC;
delete from log_event l where l.logid=logidC;
DELETE FROM TIBCO_REMEDIATION.RESUBMISSION_V2 WHERE flowid=flowidc;
TEXT
--------------------------------------------------------------------------------
EXCEPTION
when OTHERS Then
UTL_FILE.PUT_LINE(file_handle,'Exception in Purge');
statutCode:='KO';
exit;
ROLLBACK ;
end;
if (indice=indiceCommit) then
commit;
indice:=0;
UTL_FILE.PUT_LINE(file_handle,'Total lines purged '|| nbLine ||' at '|
TEXT
--------------------------------------------------------------------------------
| TO_CHAR(sysdate, 'YYYY/MM/DD_HH24:MI'));
UTL_FILE.FFLUSH(file_handle);
end if;
indice:=indice+1;
nbLine:=nbLine+1;
end loop;
close DataToPurge;
if(statutCode = 'OK') then
commit;
UTL_FILE.PUT_LINE(file_handle,'Purging is complete');
TEXT
--------------------------------------------------------------------------------
UTL_FILE.PUT_LINE(file_handle,'Total lines purged '|| nbLine ||' at '|| TO
_CHAR(sysdate, 'YYYY/MM/DD_HH24:MI'));
end if;
nbLineOut:=nbLine;
UTL_FILE.FFLUSH(file_handle);
UTL_FILE.FCLOSE(file_handle);
end; |
Partager