Wednesday, September 26, 2007

DBMS_JOB

Here's few examples of how to submit the jobs.

The following would create a job to refresh a MV everyday at Midnight starting from tomorrow

SQL> variable jno number;
SQL> exec dbms_job.submit(:jno,'DBMS_MVIEW.REFRESH(''MV_TESTING'',''C'');',trunc(sysdate)+1,'sysdate+1');

Here's another example

declare
jno number;
begin
dbms_job.submit(job => jno,
what => 'my_proc(abc);'
next_date => sysdate+1,
interval => 'sysdate+1');
end;
/

next_date = > next date/time you want to run the job to run. An argument of DATE datatype must be passed in.

Example : to_date('09/30/2007 11:24 AM','MM/DD/YYYY HH:MI AM');
OR
sysdate+1 Same time tomorrow
trunc(sysdate)+1 Midnight tomorrow
trunc(sysdate)+19/24 7 pm (1900 Hrs) today

interval => at which frequency the job should be run . The argument passed is in varchar2.
Example
'sysdate+1' 24 hours after the job's current run time
'trunc(sysdate)+1' Midnight after the day the job is run
'trunc(sysdate)+19/24' 7 PM on the day the job is run

No comments: