Results 1 to 10 of 10

Thread: Schedule Jobs?

  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Unanswered: Schedule Jobs?

    Hi,

    How can i schedule oracle job , which will run a procedure every day at 11 pm.?

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Apr 2002
    Posts
    56
    use the package SYS.DBMS_JOB

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    Thanks for the reply!
    Could you please give the syntax for it.

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I know your strapline says "no need to search the web", but surely it isn't too taxing to read the excellent documentation that Oracle provides?

  5. #5
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    I was Hoping for the same reply . Specially from Tony
    I have already gone through the documentation. I always try before posting any thing in forum. I dont think there is any example for dialy execution of procedure at PARTICULAR TIME.
    I am confused about "INTERVAL" part of the dbms_jobs.

    Moreover i really hate reading oracle documentation. Its very lenghty and boring.

    Yes!!! No need to searh web
    Thanks,
    Pagnint
    (No need to search web before posting new question)

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, maybe the docs don't cover that point

    The trick is when setting the INTERVAL parameter to truncate the date and then add on the specific time you want, e.g.

    INTERVAL => 'TRUNC(SYSDATE+1)+23/24'

    So regardless of what time the job actually runs today (23:02, 23:45, ...), that time is ignored (because of the TRUNC) and the desired time of 11pm is set by adding 23/24 (23 hours) to the truncated date.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Of course, if your job is delayed by more than an hour and runs at e.g. 00:01 on the following day, then it will not run again until 23:00 on the day after that! Hopefully that won't happen...

    If that was an issue, then you could modify the INTERVAL parameter to something like:

    INTERVAL => 'TRUNC(SYSDATE-(22/24))+23/24'

  8. #8
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Thanks for immediate response.
    Your are really Oracle Guru. I was missing Trunc command.

    Once again thanks!!!


    Thanks,
    Pagnint
    (No need to search web before posting new question)

  9. #9
    Join Date
    Oct 2003
    Posts
    87

    Sys.dbms_job

    The problem with SYS.DBMS_JOB is that it doesn't handle dependencies; it simply runs the job! I've been looking for a job scheduler that runs on W2K Server and can handle dependencies. E.g. only run the job if job ABC has completed successfully and job DEF isn't running. Anyone heard of or use a Scheduler with those capabilities?
    Oracle - DB2 - MS Access -

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In 10G there is a more powerful tool called the Scheduler. Maybe this has what you want, I'm not sure. Of course, using 10G is a pipe dream for most of us right now...

Posting Permissions

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