Creating a Job in Oracle using DBMS_JOB

问题:

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

回答1:

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.