dbms_job sumbit various dates [message #376276] |
Tue, 16 December 2008 16:00  |
mlei@txi.com
Messages: 3 Registered: December 2008
|
Junior Member |
|
|
Hi,
Recently i was asked to submit a job that run at 7:00am, 11:00am, 2:00pm, and 10:00pm every day.
I was wondering how to get it realized by using dbms_job.
Could any one shed some light on it, thanks.
mike
|
|
|
Re: dbms_job sumbit various dates [message #376277 is a reply to message #376276] |
Tue, 16 December 2008 16:25   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Some possibilities :
Possibility 1) Run it every hour and exit the procedure when the hour is not right.
Possibility 2) Write a procedure that returns the right next runtime depending on the current time and use that procedure in the INTERVAL parameter
Possibility 3) Don't set an interval, but use DBMS_JOB.NEXT_DATE inside your job procedure to set the next time the job should run.
More Information
Some Examples
[Updated on: Tue, 16 December 2008 16:25] Report message to a moderator
|
|
|
|
Re: dbms_job sumbit various dates [message #376284 is a reply to message #376279] |
Tue, 16 December 2008 16:58   |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
An example of the view idea.CREATE OR REPLACE VIEW my_next_date (interval_date)
AS
SELECT CASE
WHEN ( TO_CHAR (SYSDATE, 'hh24') BETWEEN 1 AND 12 /* catch the 9:45am run */
AND TO_CHAR (SYSDATE, 'dy') IN ('mon', 'tue', 'wed', 'thu')
) THEN TRUNC (SYSDATE) + 14.75 / 24 /* today at 2:45pm */
ELSE TRUNC (SYSDATE + 1) + 9.75 / 24 /* tomorrow at 9:45am */
END
interval_date
FROM DUAL;
declare
l_job number;
begin
dbms_job.submit( job => l_job,
what =>
'dbms_refresh.refresh(''SCOTT.MY_MV'');',
next_date => sysdate,
interval => '(select * from my_next_date)' );
commit;
dbms_output.put_line( ' your job is # '|| l_job);
end;
/
|
|
|
|