I am just a beginner in Oracle and can't seem to get this job running :-
I want to delete old rows from my database. And created a job as follows :-
CREATE OR REPLACE PROCEDURE delete_trips
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Here');
DELETE FROM trip y where sysdate > y.arrival_date;
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'delete_old_rows',
job_type => 'PLSQL_BLOCK',
job_action => 'EXECUTE delete_trips;',
start_date => sysdate,
repeat_interval => 'FREQ=SECONDLY',
end_date => add_months(sysdate,12),
auto_drop => FALSE,
enabled => TRUE
);
END;
/
After executing the above code I can see that the job 'delete_old_rows' is scheduled in DBA_SCHEDULER_JOBS, but neither the rows get deleted, nor output is produced in the terminal. I tried logging out and logging in again, and yet no row was deleted and no output was produced. And rows satisfying the condition for delete do exist. Please help. What am I missing ?
Best Answer
Try this:
You don't need the
EXECUTE
part injob_action
.You can check
DBA_SCHEDULER_JOBS.NEXT_RUN_DATE
to check when it's next due to be executed. Obviously you can also check the other relevant columns to see if it's actually executed and/or failed (LAST_START_DATE
,FAILURE_COUNT
etc). Table doc link.