DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: dbms_jobs question

  1. #1
    Join Date
    Jan 2005
    Posts
    221

    dbms_jobs question

    below is my dbms_jobs:

    var x number
    begin
    dbms_job.submit(:x, 'test;', TRUNC(sysdate)+10/24, 'TRUNC(sysdate)+1+10/24');
    commit;
    end;
    /


    how can you change this to be run at 10:30am, 3:30PM and 8:30PM every day????

    thanks

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Since, the time interval is NOT uniform, I would suggest to setup 3 jobs.

    Tamil

  3. #3
    Join Date
    Jan 2005
    Posts
    221
    thanks tamil,

    the last question is "how do you schedule the mentioned job to 10:30AM instead of 10:00AM???

    thanks

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by hannah00
    thanks tamil,

    the last question is "how do you schedule the mentioned job to 10:30AM instead of 10:00AM???

    thanks
    a marginal amount of effor plz - look at the code you provided...what do you think you change to make the code work as desired?
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    If you want to have only one job that self-adjusts its next run, use some kind of conditional SQL logic for the interval, like DECODE or CASE.

    By using CASE, your could change your interval by using something like this:
    Code:
    dbms_job.interval
      (:x,
       'case
          when trunc(sysdate, ''hh24'') < 10 then
             trunc(sysdate) + 10/24+30/1440
          when trunc(sysdate, ''hh24'') < 15 then
             trunc(sysdate) + 15/24+30/1440
          when trunc(sysdate, ''hh24'') < 20 then
             trunc(sysdate)+ 20/24+30/1440'
          else
             trunc(sysdate) + 1 + 10/24+30/1440
        end');
    Last edited by jmodic; 01-24-2005 at 03:21 PM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Good CASE statement from Modic.

    How about this:

    The interval should be: trunc(sysdate)+1+21/48 .


    SQL> select trunc(sysdate)+1+21/48 from dual ;

    TRUNC(SYSDATE)+1+21/
    --------------------
    25-JAN-2005 10:30:00

    Tamil
    Last edited by tamilselvan; 01-24-2005 at 03:26 PM.

  7. #7
    Join Date
    Jan 2005
    Posts
    221
    you guys are great

    thanks so much

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