Creating a Job in Oracle using DBMS_JOB

Question

I'm trying to create a job that will run a certain procedure every Christmas. This is how far I have gotten:

declare
   jobno number;
begin
   dbms_job.submit( jobno,
                   'BEGIN GiveCoins; END;',
                    to_date('12/25', 'MM/DD'),
                    'sysdate + ?');
end;
/

However I can't seem to find an easy way to change the interval to yearly and am just generally quite confused about how to go about this, any help greatly appreciated

@gdoron: You might be able to, if you have the sysdba role, but you shouldn't. You shouldn't ever directly modify the system catalog. — Allan, May 10 '12 at 17:38
@Allan. thanks for the tip. (Now I understand why I got error when I tried to do it long time ago... {two days ago}) And how do you remove a job? — gdoron, May 10 '12 at 17:40
@Allan. What is the reason `ALL_JOBS` shouldn't be directly changed? what can it cause? — gdoron, May 10 '12 at 20:38
@gdoron: You don't change it because it's part of the data dictionary. To quote Oracle, "Altering or manipulating the data in data dictionary tables can permanently and detrimentally affect the operation of a database." The most obvious repercussion would be that you would almost certainly disqualify the entire instance from receiving Oracle support. Beyond that I don't know what would happen and am not particularly motivated to find out. — Allan, May 10 '12 at 21:11

score 7 · Accepted Answer · answered May 10 '12 at 16:32

You'd want something like

declare
   jobno number;
begin
   dbms_job.submit( jobno,
                   'BEGIN GiveCoins; END;',
                    to_date('12/25/2012', 'MM/DD/YYYY'),
                    'add_months(trunc(sysdate),12)');
end;
/

This will run the job for the first time at midnight on Christmas 2012 and every 12 months after that.

Creating a Job in Oracle using DBMS_JOB

1 Answers1

Linked