Create Job Scheduller

Langsung aja deh, gw lagi males pake kalimat pembukaan, kali ini gw mau kasih contoh untuk membuat ‘job scheduller’ pada database oracle.

Sample Code

BEGIN
sys.dbms_scheduler.create_job(
job_name => ‘“GUNAWAN”."CUSTOMERNAMECARDUPDATE"’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘insert into customer_name_card
select * from customer_name_card@mccconect
where orig_input_date >= ‘’1-oct-2000’’’,
start_date => to_timestamp_tz(’2006-12-20 17:17:00 +7:00’, ‘YYYY-MM-DD HH24:MI:SS TZH:TZM’),
job_class => ‘DEFAULT_JOB_CLASS’,
comments => ‘Mengupdate customer_name_card’,
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => ‘“GUNAWAN”."CUSTOMERNAMECARDUPDATE"’, attribute => ‘logging_level’, value => DBMS_SCHEDULER.LOGGING_FULL);
sys.dbms_scheduler.enable( ‘“GUNAWAN”."CUSTOMERNAMECARDUPDATE”’ );
END;

BEGIN
sys.dbms_scheduler.create_job(
job_name => ‘“GUNAWAN”."IMPORTAIG"’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘insert into master_trx
select custnum, ‘’4. jan-mar06’’ as periode, ‘’CREDIT CARD’’ as tndrtype
from mcc_tm_tdrtypebycpc
where (periode between ‘’2006-01’’ and ‘’2006-03’’) and
(tndrtype in (’’2’’,’’3’’,’’4’’,’’5’’,’’6’’,’’7’’,’’8’’,’’9’’,’’10’’,’’11’’)) and
(custnum not in (select distinct custnum from mcc_tm_tdrtypebycpc_unik))
group by custnum’,
start_date => to_timestamp_tz(’2007-02-17 20:00:00 +7:00’, ‘YYYY-MM-DD HH24:MI:SS TZH:TZM’),
job_class => ‘DEFAULT_JOB_CLASS’,
comments => ‘import for aig’,
auto_drop => FALSE,
enabled => TRUE);
END;