Friday 11 April 2008

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

No comments: