Skip to Main Content
  • Questions
  • DBMS_JOB to run first Saturday of every month

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: December 31, 2006 - 12:54 am UTC

Last updated: August 29, 2008 - 12:53 pm UTC

Version: 10.1.0.5.0

Viewed 10K+ times! This question is

You Asked

Hi,

I'd like to set-up a DBMS_JOB to run the first Saturday of every month. The problem that I'm having, is in how to pass this into the interval parameter in my call to dbms_job.submit. Any help appreciated.

Thanks,

J.

and Tom said...

next_day(last_day(sysdate),'sat')


take sysdate, make it become the last day of the current month, find the next saturday

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Interesting

Tom Fox, January 02, 2007 - 7:31 am UTC

I like that. It seems the "small stuff" to some becomes the "big stuff" to others (myself included). Nothing like a good brain reset to start off the day.

But

Rajesh, January 02, 2007 - 8:50 am UTC

But Tom,

What if I am starting this from Jan 02nd onwards and I need the job to run on Jan 06th as well (First Saturday for Jan 2007). How do I make it generic?

SQL> select next_day(last_day(sysdate),'sat') from dual;

NEXT_DAY(LA
-----------
03-FEB-2007

SQL> select next_day(sysdate,'sat') from dual;

NEXT_DAY(SY
-----------
06-JAN-2007

Tom Kyte
January 04, 2007 - 9:35 am UTC

then your interval would be the same but the next_date would be just next_day(sysdate,'SAT')

Running for current month

Rory, January 02, 2007 - 11:33 am UTC

Rajesh, take a look at the dbms_job.submit parameters. You specify several important things, one being the NEXT_DATE it is to run. Another being the INTERVAL between job runs. All you have to do is put in January 6th as the next_date, and the 'next_day(last_day(sysdate),''sat'')' string as the interval.

If you are using 10g...

Reader, January 02, 2007 - 2:29 pm UTC

Can't you do this?

FREQ=MONTHLY;BYDAY=1SAT

First Saturday of every month. Short and sweet!

Re: If you are using 10g...

Rory, January 02, 2007 - 4:11 pm UTC

Yes, but that's using dbms_scheduler rather than dbms_job. Better in almost every way, imho, but lots of us still have to live with 9i and dbms_job. :-)

Use LEAST

Rizwan Qazi, January 04, 2007 - 12:43 am UTC

user_a@db> select least(next_day(last_day(sysdate),'sat'), next_day(sysdate,'sat')) next_sat from dual;

next_sat
---------
06-JAN-07


Tom Kyte
January 05, 2007 - 9:00 am UTC

why?

Did not see your followup

Rizwan Qazi, January 06, 2007 - 12:04 am UTC

Oh, I am sorry - did not see your followup to his earlier question:

>> Followup:
then your interval would be the same but the next_date would be just next_day(sysdate,'SAT')

dbms_jobs interval

vijaya, August 27, 2008 - 12:59 pm UTC

I want to schedule job to run every 3rd saturday on the month. What is the correct syntax? Following does not set the correct inverval.
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'sys.dbms_stats.gather_schema_stats(ownname=>''PSFT_LND'',cascade=>true,degree=>8, estimate_percent => dbms_stats.auto_sample_size, GRANULARITY => ''ALL'' ,method_opt=>'' for all columns size 75'');'
,next_date => to_date('27/08/2008 14:04:11','dd/mm/yyyy hh24:mi:ss')
,interval => 'NEXT_DAY (TRUNC(TO_DATE((TO_CHAR(SYSDATE,''YYYYMM'')||''21''),''YYYYMMDD'')),''SAT'') +21/24'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

commit;
Tom Kyte
August 29, 2008 - 12:53 pm UTC

have you looked at the scheduler instead - it would be rather "easy" to do this with the scheduler.

But basically, you want to take sysdate of when the job is running - get the last day of that month, find the next_day that is saturday (that'll be the FIRST saturday in the next month) and add 14 days to it, that'll be the third saturday.

ops$tkyte%ORA10GR2> select sysdate, next_day(last_day(trunc(sysdate)),'sat')+14
  2  from dual;

SYSDATE   NEXT_DAY(
--------- ---------
29-AUG-08 20-SEP-08

ops$tkyte%ORA10GR2> !cal 9 2008
   September 2008
Su Mo Tu We Th Fr Sa
    1  2  3  4  5  6
 7  8  9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30




More to Explore

DBMS_JOB

More on PL/SQL routine DBMS_JOB here