3 Replies Latest reply: Nov 12, 2012 3:25 PM by 849870 RSS

    DBMS_JOB next interval for first business day of next quarter

    849870
      Experts,

      I want to call one procedure using DBMA_JOB which should run every first business day of next quarter.I created a function which gives the first business day of next quarter taking sysdate as input. My question is how do I schedule it in DBMS_JOB such a way that the next_date parameter will display the next business day of next quarter and what should I pass to the interval parameter?
        • 1. Re: DBMS_JOB next interval for first business day of next quarter
          Sg049
          What version of Oracle you are using?

          Use "DBMS_Scheduler". It provides the great functionality and flexibility for scheduling jobs. Use the same logic what you have in the function in DBMS_Scheduler.

          for more info on DBMS_SCHEDULER

          http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm
          • 2. Re: DBMS_JOB next interval for first business day of next quarter
            Frank Kulash
            Hi,
            user13599912 wrote:
            Experts,

            I want to call one procedure using DBMA_JOB
            As mentioned above, consider using dbms_scheduler instead of dbms_job.
            which should run every first business day of next quarter.I created a function which gives the first business day of next quarter taking sysdate as input.
            You can have it run on the first day of the next quarter without a user-defined function, like this:
            interval  => 'TRUNC ( ADD_MONTHS (SYSDATE, 3)
                               , 'Q'
                          )'
            This assumes your quarters start in Juanary, April, July and October.

            SYSDATE       returns the current date, which, by definition, is in the current quarter.
            ADD_MONTHS (SYSDATE, 3)       returns a DATE in the next quarter.
            TRUNC (ADD_MONTHS (SYSDATE, 3), 'Q')       returns the beginning of that quarter.
            My question is how do I schedule it in DBMS_JOB such a way that the next_date parameter will display the next business day of next quarter and what should I pass to the interval parameter?
            You can call a user-defined function the same way you call built-in functions, such as SYSDATE, ADD_MONTHS and TRUNC. Assuming your function returns a DATE, you might use:
            interval  => 'my_function'
            • 3. Re: DBMS_JOB next interval for first business day of next quarter
              849870
              The version is Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi. I have to use dbms_job as all the previous jobs are written in that fashion.
              Script that I used for creating job
              DECLARE
              X NUMBER;
              BEGIN
              SYS.DBMS_JOB.SUBMIT
              ( job => X
              ,what => 'begin rpts.detail_eaco; end;'
              ,next_date => to_date('12/11/2012 02:00:00','dd/mm/yyyy hh24:mi:ss')
              ,interval => 'Z_Rpt_QTRBusDay_function'
              ,no_parse => FALSE
              );
              SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
              COMMIT;
              END;


              The function Z_Rpt_QTRBusDay_function gives the next business day of every quarter. My question is if I use the function in interval attribute I should get the next_date as 1/2/2013. Once the job ran on 1/2/2013 the next_date should be 4/1/2013. Help me in acheiving this.

              Edited by: user13599912 on Nov 12, 2012 1:24 PM