dbms_job intervals

[mrpaulwass]

I have the following SQL which I'm hoping will run a job every day at 7:00am, but I'm not sure what the LEAST statement is doing. Can anyone explain why I need this, or what it's doing? (Obviously I borrowed the code from an example).

Thanks,

Paul


DECLARE
jobno NUMBER;
BEGIN
dbms_job.submit(jobno
,'MY_PACKAGE.MY_JOB;'
,SYSDATE
,'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''MONDAY'')'||
',NEXT_DAY(SYSDATE, ''TUESDAY'')'||
',NEXT_DAY(SYSDATE, ''WEDNESDAY'')'||
',NEXT_DAY(SYSDATE, ''THURSDAY'')'||
',NEXT_DAY(SYSDATE, ''FRIDAY'')))+7/24'
);
END;

[Tarry]

check out this example
http://download-west.oracle.com/docs...tion.htm#83889

[mrpaulwass]

Thank you for the response, but I still don't understand how least works in the given example.

I would like my job to process every Monday, Tuesday, Wednesday, Thursday, and Friday at 7:00am.

(I do understand the LEAST command)

Thanks,

P

[gandolf989]

I broke up the sql and got the following results. So the least date for today would be '13-NOV-02' which is tomorrow. When you run it on Friday it will be '18-NOV-02'

SQL> SELECT NEXT_DAY(SYSDATE, 'WEDNESDAY') FROM DUAL;
13-NOV-02
SQL> SELECT NEXT_DAY(SYSDATE, 'THURSDAY') FROM DUAL;
14-NOV-02
SQL> SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM DUAL;
15-NOV-02
SQL> SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM DUAL;
18-NOV-02
SQL> SELECT NEXT_DAY(SYSDATE, 'TUESDAY') FROM DUAL;
19-NOV-02

[jmodic]

TRUNC(LEAST(NEXT_DAY(SYSDATE, 'MONDAY'),
            NEXT_DAY(SYSDATE, 'TUESDAY'),
            NEXT_DAY(SYSDATE, 'WEDNESDAY'),
            NEXT_DAY(SYSDATE, 'THURSDAY'),
            NEXT_DAY(SYSDATE, 'FRIDAY')))

This expression will return you the next working day's date. If you run it on Monday through Thursday it wil simply return you the next day, while on Friday, Saturday or Sunday it will return you the next Monday's date. If you are not to comfortable with this piece of code, you can rewrite it using a DECODE:

DECLARE
jobno NUMBER;
BEGIN
dbms_job.submit(jobno
,'MY_PACKAGE.MY_JOB;'
,SYSDATE
,'TRUNC(DECODE(TO_CHAR(SYSDATE,'DAY'),
               ''FRIDAY'', SYSDATE+3,
               ''SATURDAY'', SYSDATE+2,
               SYSDATE+1
              )
       )+7/24'
);
END;