1

I have a query that removes my data in DB from 20 days ago every time I use it. Can you please advise me on how I can automate this task? What would be a better solution? Using procedures or packages?

I'm sorry for the beginner questions, I've just started working on it.

Many thanks.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
Forgiven Des
  • 19
  • 4
  • 1
    Look at using `DBMS_SCHEDULER`: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SCHEDULER.html; https://oracle-base.com/articles/19c/scheduler-enhancements-19c – pmdba Jun 27 '22 at 13:16
  • Thanks for the reply. What should I look for if I wanted to implement this by using packages? – Forgiven Des Jun 27 '22 at 13:17

1 Answers1

4

Suppose you have your logic inside user MY_USER package MY_PACKAGE in procedure MY_PROCEDURE. Then you can schedule a job using this code:

begin 
    DBMS_SCHEDULER.CREATE_JOB(
                        job_name=>'MY_USER.MY_JOB',
                        job_type=>'PLSQL_BLOCK',
                        job_action=>'MY_USER.MY_PACKAGE.MY_PROCEDURE;',
                        start_date        =>  sysdate,
                        repeat_interval   =>  'FREQ=DAILY');
end;

You can monitor your job using :

SELECT * FROM DBA_scheduler_jobs
where owner='MY_USER';

You can drop the job using :

begin 
   DBMS_SCHEDULER.DROP_JOB('MY_USER.MY_JOB');
END;
Michael
  • 2,835
  • 2
  • 9
  • 15