6 Replies Latest reply: Nov 13, 2012 11:04 PM by user10447332 RSS

    dbms_job.submit paarmeters

    user10447332
      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
          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
            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
              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
                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
                  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
                    i have created the job with the code but its not running