This discussion is archived
6 Replies Latest reply: Nov 13, 2012 9:04 PM by user10447332 RSS

dbms_job.submit paarmeters

user10447332 Newbie
Currently Being Moderated
hi,

i want to call one procedure using dbms_job.submit.this procedure should run twice a day.what are teh values i have to pass to next_date parameter, interval parameter to dbms_job.submit
  • 1. Re: dbms_job.submit paarmeters
    Hoek Guru
    Currently Being Moderated
    The interval you need to set will depend on the times you want your procedure to run.
    Can you expand on that?
    You won't need to set next_date.
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_job.htm#autoId13

    If you do a search on http://asktom.oracle.com on e.g. 'dbms_job.submit interval', you'll get lots of examples.
    Also searching this forum should give you ideas.
  • 2. Re: dbms_job.submit paarmeters
    Solomon Yakobson Guru
    Currently Being Moderated
    VARIABLE jobno number;
    BEGIN
        DBMS_JOB.SUBMIT(
                        job       => :jobno, 
                        what      => 'whatever-you-want-to-execute', 
                        next_date => SYSDATE, -- run now
                        interval  => 'SYSDATE + 1/2' -- run every 12 hours
                       );
        COMMIT;
    END;
    /
    SY.
  • 3. Re: dbms_job.submit paarmeters
    user10447332 Newbie
    Currently Being Moderated
    i need to run that job everyday 2 times ie morning 6a m and evening 6pm every day.
    how to schedule that.
  • 4. Re: dbms_job.submit paarmeters
    Solomon Yakobson Guru
    Currently Being Moderated
    user10447332 wrote:
    i need to run that job everyday 2 times ie morning 6a m and evening 6pm every day.
    how to schedule that.
    VARIABLE jobno number;
    BEGIN
        DBMS_JOB.SUBMIT(
                        job       => :jobno, 
                        what      => 'begin null; end;', 
                        next_date => CASE WHEN SYSDATE - TRUNC(SYSDATE) <= 1/4 THEN TRUNC(SYSDATE) + 1/4 WHEN SYSDATE - TRUNC(SYSDATE) <= 3/4 THEN TRUNC(SYSDATE) + 3/4 ELSE TRUNC(SYSDATE) + 1 + 1/4 END, --start to closest time of 6a.m. or 6p.m.
                        interval  => 'CASE WHEN SYSDATE - TRUNC(SYSDATE) <= 1/4 THEN TRUNC(SYSDATE) + 1/4 WHEN SYSDATE - TRUNC(SYSDATE) <= 3/4 THEN TRUNC(SYSDATE) + 3/4 ELSE TRUNC(SYSDATE) + 1 + 1/4 END
                                     ' -- run every day at 6a.m. and 6p.m.
                       );
        COMMIT;
    END;
    /
    SY.
  • 5. Re: dbms_job.submit paarmeters
    user10447332 Newbie
    Currently Being Moderated
    i have created the scheduler yestarday ie 11/13/2012 (mm/dd/yyyy) ,by uisng the code but its not running.
    it shows the next_date as 11/13/2012 6:00:00 PM. it means its nor run.

    i
  • 6. Re: dbms_job.submit paarmeters
    user10447332 Newbie
    Currently Being Moderated
    i have created the job with the code but its not running

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points