How to specify interval when creating a job

Toad for Oracle
1

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:

2

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:
__
_______________________________________

3

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

4

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

5

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

6

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:

7

‘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.

8

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

9

@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.