Toad World® Forums

How to specify interval when creating a job

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => ‘’
,next_date => to_date(‘08/01/2012 02:00:00’,‘dd/mm/yyyy hh24:mi:ss’)
,interval => ???
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ’ || to_char(x));
END;

Hello,

I’m trying to create a job thru TOAD and the job should run only every 2nd Sunday of January and July.

How can I specify that in the interval?

Thanks in advance for the help! :slight_smile:

You must define a function that calculates the next day given the current date
and use that in the next day definition.

Groetjes,
Wim

On Thu, Aug 18, 2011 at 12:18, beninimus_109
wrote:

Message from: beninimus_109

Hello,

I'm trying to create a job thru TOAD and the job should run only every
2nd Sunday of January and July.

How can I specify that in the interval?

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X
     ,what      => ''
     ,next_date => to_date('08/01/2012
02:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => ???
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;

Thanks in advance for the help! :slight_smile:

_______________________________________

Historical Messages

Author: beninimus_109
Date: Thu Aug 18 03:18:32 PDT 2011
Hello,

I'm trying to create a job thru TOAD and the job should run only every
2nd Sunday of January and July.

How can I specify that in the interval?

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X
     ,what      => ''
     ,next_date => to_date('08/01/2012
02:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => ???
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;

Thanks in advance for the help! :slight_smile:
__
_______________________________________

You may want to consider DBMS_SCHEDULER if you’re on Oracle 10 or 11g. I
think it has much more flexibility in this area

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm#i10130
37

Here is a start in the right direction

INTERVAL => NEXT_DAY(To_Date( ‘07/’ || To_Char(Add_Months(sysDate, 1 ),
‘MM/YYYY’ ), ‘DD/MM/YYYY’ ), ‘SUNDAY’ )

Thanks,

Carl
image001.jpeg

Here is a start in the right direction

INTERVAL => NEXT_DAY(To_Date( ‘07/’ || To_Char(Add_Months(sysDate, 1 ),
‘MM/YYYY’ ), ‘DD/MM/YYYY’ ), ‘SUNDAY’ )

Thanks,

Carl
image001.jpeg

Creative use of the Oracle functions got me this far:

‘next_day(trunc(last_day(add_months(sysdate, 5 -mod(extract(month from
add_months(sysdate,- 1 )), 6 )))), ‘SUNDAY’ )+ 7 ’

This will:

‘next_day(trunc(last_day(add_months(

sysdate, 5 -mod(extract(month from add_months(

sysdate,- 1 )), 6 )))), ‘SUNDAY’ )+ 7 ’

My advice: Follow Wim’s suggestion. Keep your code simple and author a
function that will properly return the date in all instances. Not only does it
simplify the readability of the code piece you’re using, but it can be
re-used elsewhere for exactly the same reason.

If you have one accounting/reporting need for such a specific date structure,
odds are you’re going to end up with more (if you don’t already have
them). Dropping it in a function will allow multiple users from multiple areas
(sql/forms/reports/web gui) to be able to use the same function consistently.

Roger S.

This works, but Oracle will throw an error if you try to make an interval
expression that’s longer than 200 characters. So you could make a function
out of it.

select case

when --the july date this year has passed

((sysdate - next_day(to_date( ‘07/07/’ || to_char(sysdate, ‘yyyy’ ),
‘dd//mm/yyyy’ ), ‘SUNDAY’ )) > 0 ) then

– return the january date for next year

next_day(to_date( ‘07/01/’ || to_char(to_number(to_char(sysdate, ‘yyyy’ )) +
1 ), ‘dd//mm/yyyy’ ), ‘SUNDAY’ )

when --the january date this year has passed

((sysdate - next_day(to_date( ‘07/01/’ || to_char(sysdate, ‘yyyy’ ),
‘dd//mm/yyyy’ ), ‘SUNDAY’ )) > 0 ) then

– return the july date for this year

next_day(to_date( ‘07/07/’ || to_char(sysdate, ‘yyyy’ ), ‘dd//mm/yyyy’ ),
‘SUNDAY’ )

else

– return the january date for this year

next_day(to_date( ‘07/01/’ || to_char(sysdate, ‘yyyy’ ), ‘dd//mm/yyyy’ ),
‘SUNDAY’ )

end next_second_sun_jan_or_july

from dual

@Wim, thanks for the suggestion, I’ll try to look into it…

@Hillbilly, thanks for the link, I’ll check it out too… We’re on Oracle 10g.

@Carl, thanks for the help…

@Nath, tried it and got January 8, 2012 which is the second sunday of January next year… will check if it also works for the 2nd sunday of July.

@Roger, I’m trying not to create a function but if I have no choice then I’ll go for it… thanks for the advice.

@jdorlon, thanks, this helps a lot…

To all,
Appreciate the assistance, you all really helped me start at the right direction, I’ll look into all your suggestions.