Home » SQL & PL/SQL » SQL & PL/SQL » dbms_job sumbit various dates (10.2.0)
dbms_job sumbit various dates [message #376276] Tue, 16 December 2008 16:00 Go to next message
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 Go to previous messageGo to next message
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 #376279 is a reply to message #376277] Tue, 16 December 2008 16:27 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Oh,

And Possibility 4) Create 4 jobs that run every 24 hours on those times. Wink
Re: dbms_job sumbit various dates [message #376284 is a reply to message #376279] Tue, 16 December 2008 16:58 Go to previous messageGo to next message
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;
/

Re: dbms_job sumbit various dates [message #376336 is a reply to message #376276] Wed, 17 December 2008 01:03 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/313430/102589/?#msg_313430

Regards
Michel
Previous Topic: NULL SELF ERROR
Next Topic: Cloning a table
Goto Forum:
  


Current Time: Tue Feb 11 08:49:29 CST 2025