Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: How to specify a "LAST_DATE" while submitting a job using " DBMS_JOB.SUBMIT" routine

    Dear All,

    I seek your advice on a recent problem I have faced while submitting a job with DBMS_JOB package as DBMS_SCHEDULER is not available in Oracle 9i and version of my database is 9i.

    My requirement is to create a job which will run daily for 30 days only (beginning from the day its started). For Example if I submit the job today then it will run daily once for next 30 days only.

    As there is no parameter called : last_date in DBMS_JOB.SUBMIT routine, please help me how to do the same with existing parameters: next_date and interval.

    Please find below the snippet I am using.
    __________________________________________--
    DECLARE
    jobno NUMBER;
    BEGIN
    DBMS_JOB.SUBMIT
    (job => jobno
    ,what => 'begin PKG_Name.Procedure_Name; end;'
    ,next_date => SYSDATE
    ---,last_date => SYSDATE+30
    ,interval => 'SYSDATE+1');
    COMMIT;
    END;
    /
    __________________________________________________ ______--

    Many Thanks,
    Regards
    Saswati

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DECLARE
    jobno NUMBER;
    BEGIN
    DBMS_JOB.SUBMIT
    (job => jobno
    ,what => 'begin PKG_Name.Procedure_Name; end;'
    ,next_date => SYSDATE
    ---,last_date => SYSDATE+30
    ,interval => 'SYSDATE+1');
    DBMS_JOB.BROKEN
    (job => jobno
    ,next_date => SYSDATE+30+1/24);
    COMMIT;
    END;
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2012
    Posts
    2
    Thank you for your reply..
    But when I try to execute this , the job which is previously submitted becomes broken instantly . But as per my requirement, it should be broken after 30 days..Can you please help in this regard?
    Also its helpful if you can explain the column" next date" in the subroutine: DBMS_JOB.Broken

    Thanks in Advance,
    Saswati

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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