scheduling jobs in the database with DBMS_JOB

Quickly submitting jobs with the Oracle DBMS_JOB package. The example below is an oracle schema user who wants to schedule the execution of a PL/SQL procedure at certain times during the day.


From SQL*Plus :

SQL> variable n number
SQL> exec dbms_job.submit( :n, 'YOURPLSQLPROCEDUREHERE;', sysdate, 'trunc(sysdate)+1+1/288' );
SQL> commit;

From PL/SQL :

declare
   l_job number;
begin
   dbms_job.submit( l_job,
                   'YOURPLSQLPROCEDUREHERE;',
                    trunc(sysdate)+20/24,
                   'trunc(sysdate)+1+20/24' );
commit;
end;

Some Scheduling time semantics
# 1/288 means every 5 minutes
# 1/24 means every hour
# trunc(sysdate)+1+11/24 means at 11am every day