Wednesday, July 29, 2009

ORA-01839 When Using Interval

I created a job that deletes 3 months past data.
During testing, i noticed that when using intervals, job fails in some days.

BEGIN
dbms_scheduler.create_job(
job_name => 'purge_gps_archive_3_month',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN delete from table_log where datetime_local<sysdate- interval "3" month; END;',
start_date=> to_date('28/07/2009 23:59','DD/MM/YYYY HH24:MI'),
repeat_interval => 'FREQ = DAILY; INTERVAL = 1',
enabled => TRUE,
auto_drop => FALSE,
comments => 'This job deletes data from log table that are older than 3 months');
END;

After i use add_months instead of interval, problem solved.

BEGIN
dbms_scheduler.create_job(
job_name => 'purge_gps_archive_3_month',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN delete from table_log where datetime_local<add_months(sysdate,-3); END;',
start_date=""> to_date('28/07/2009 23:59','DD/MM/YYYY HH24:MI'),
repeat_interval => 'FREQ = DAILY; INTERVAL = 1',
enabled => TRUE,
auto_drop => FALSE,
comments => 'This job deletes data from log table that are older than 3 months');
END;