0

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 ?

1

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.

| improve this answer | |
  • Thank you! It worked :) – Jeevesh Juneja Feb 22 at 19:17
  • But, although the rows were deleted, there was no line printed on the terminal.. any idea as to why it is so? Or where the output might be going? – Jeevesh Juneja Feb 22 at 19:21
  • The jobs get scheduled in the background & are executed by a non-interactive process, so you will not see the output in your terminal – Philᵀᴹ Feb 23 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.