Catatan Ku

Untuk membuat tools monitoring kita akan buat sebuah tabel yang hanya berisi current update status. Jadi ketika kita refresh data dalam tabel tersebut tidak terlalu banya.
Berikut langkah2nya :
1. buat trigger agar tabel production/ tabel seluruh data kedalam tabel current yang hanya menampung HANYA data current.
-- BUAT TRIGGER
CREATE OR REPLACE TRIGGER INSERT_V_GPRS_REQ_TEMP_CURRENT
AFTER INSERT ON z_gprs_intec_temp
FOR EACH ROW
DECLARE
GPRS_CODE CHAR (5);
TR_DATE DATE;
TOT_REQ_CNT NUMBER;
TOT_RSP_CNT NUMBER;
GENEVA_REQ_CNT NUMBER;
GENEVA_RSP_CNT NUMBER;
BYPASSED_CNT NUMBER;
BFT_CNT_OCS NUMBER;
BFT_CNT_GNV NUMBER;
BFT_CNT_GNV_TMOT NUMBER;
BEGIN
-- The insert on the nested table is translated to an insert on the base table:
INSERT INTO V_GPRS_REQ_TEMP_CURRENT
(GPRS_CODE, TR_DATE, TOT_REQ_CNT, TOT_RSP_CNT, GENEVA_REQ_CNT, GENEVA_RSP_CNT,
BYPASSED_CNT, BFT_CNT_OCS, BFT_CNT_GNV, BFT_CNT_GNV_TMOT)
VALUES (
:new.gprs_code, :new.tr_date,
:new.tot_req_cnt, :new.tot_rsp_cnt,
:new.geneva_req_cnt,
:new.geneva_rsp_cnt, :new.bypassed_cnt,
:new.bft_cnt_ocs, :new.bft_cnt_gnv,
:new.bft_cnt_gnv_tmot);
END;
/  

2. buat prosedure
-- BUAT PROSEDURE
CREATE OR REPLACE Procedure HCPTOCC.CLEAR_V_GPRS_REQ_TEMP_CURRENT is
BEGIN
delete from V_GPRS_REQ_TEMP_CURRENT
where tr_Date<sysdate-7 ;
END;
/

3. buat shedule job

----BUAT SCHEDULE JOBS
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'CLEARIN_GPRS_REQ_TEMP_CURRENT'
,start_date => TO_TIMESTAMP_TZ('2013/04/06 18:52:32.120981 +07:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'freq=HOURLY;interval=1'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN CLEAR_V_GPRS_REQ_TEMP_CURRENT; END;'
,comments => 'Job defined entirely by the CREATE JOB procedure.'
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'CLEARIN_GPRS_REQ_TEMP_CURRENT'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'CLEARIN_GPRS_REQ_TEMP_CURRENT'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'CLEARIN_GPRS_REQ_TEMP_CURRENT'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'CLEARIN_GPRS_REQ_TEMP_CURRENT'
,attribute => 'MAX_RUNS');
BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'CLEARIN_GPRS_REQ_TEMP_CURRENT'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
EXCEPTION
-- could fail if program is of type EXECUTABLE...
WHEN OTHERS THEN
NULL;
END;
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'CLEARIN_GPRS_REQ_TEMP_CURRENT'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'CLEARIN_GPRS_REQ_TEMP_CURRENT'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'CLEARIN_GPRS_REQ_TEMP_CURRENT'
,attribute => 'AUTO_DROP'
,value => TRUE);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'CLEARIN_GPRS_REQ_TEMP_CURRENT');
END;
/