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
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
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;
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