-
dbms_job intervals
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 Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
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
Last edited by mrpaulwass; 11-12-2002 at 01:02 PM.
-
Re: dbms_job intervals
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
-
Code:
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:
Code:
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;
Last edited by jmodic; 11-12-2002 at 04:00 PM.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|