Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    78

    Unanswered: want to run the job every 4pm & 9am

    Hai

    The job is run every 24 hours.I want to run the job every day evening 4pm and morning 9am.How to do it?

    Thanks in advance
    mohan

    SET SERVEROUTPUT ON
    DECLARE
    v_job NUMBER;
    BEGIN

    DBMS_Job.Submit(v_job,
    'BEGIN DBMS_Stats.Gather_Schema_Stats(''MOHAN''); END;',
    Sysdate,
    'Sysdate + 1');
    COMMIT;
    DBMS_Output.Put_Line('Job: ' || v_job);
    END;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: want to run the job every 4pm & 9am

    The logic is: if current time < 4pm then run at 4pm today; else run at 9am tomorrow.

    So this should do it:

    next_date => DECODE( SIGN(TRUNC(SYSDATE)+16/24 - SYSDATE), 1, TRUNC(SYSDATE)+16/24, TRUNC(SYSDATE)+1+9/24 )'

    Or if your PL/SQL supports CASE:

    next_date => 'CASE WHEN SYSDATE< TRUNC(SYSDATE)+16/24 THEN TRUNC(SYSDATE)+16/24 ELSE TRUNC(SYSDATE)+1+9/24 END'

  3. #3
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: want to run the job every 4pm & 9am

    Originally posted by andrewst
    The logic is: if current time < 4pm then run at 4pm today; else run at 9am tomorrow.

    So this should do it:

    next_date => DECODE( SIGN(TRUNC(SYSDATE)+16/24 - SYSDATE), 1, TRUNC(SYSDATE)+16/24, TRUNC(SYSDATE)+1+9/24 )'

    Or if your PL/SQL supports CASE:

    next_date => 'CASE WHEN SYSDATE< TRUNC(SYSDATE)+16/24 THEN TRUNC(SYSDATE)+16/24 ELSE TRUNC(SYSDATE)+1+9/24 END'
    Hi,

    Also check the JOB_QUEUE_INTERVAL parameter value in the init.ora file.
    nn

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •