Creating a Job in Oracle using DBMS_JOB

sql oracle dbms-job
JessMcintosh picture JessMcintosh · May 10, 2012 · Viewed 28.3k times · Source

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

Answer

Justin Cave picture Justin Cave · May 10, 2012

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.

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More