Search code examples
sqloracledbms-job

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

Solution

  • 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.