Oracle SQL*PLUS – Scheduled Job Not Working

jobsoraclesqlplus

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:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'delete_old_rows',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'delete_trips;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=SECONDLY',
        end_date        => add_months(sysdate,12),
        auto_drop       => FALSE,
        enabled         => TRUE
    );
END;
/

You don't need the EXECUTE part in job_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.

Related Question