dbms_job intervals
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: dbms_job intervals

  1. #1
    Join Date
    Nov 2001
    Location
    New Brunswick, NJ
    Posts
    67

    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;

  2. #2
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  3. #3
    Join Date
    Nov 2001
    Location
    New Brunswick, NJ
    Posts
    67
    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 12:02 PM.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015

    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

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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 03: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
  •  



Click Here to Expand Forum to Full Width