This content has been marked as final. Show 3 replies
user13599912 wrote:As mentioned above, consider using dbms_scheduler instead of dbms_job.
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.You can have it run on the first day of the next quarter without a user-defined function, like this:
This assumes your quarters start in Juanary, April, July and October.
interval => 'TRUNC ( ADD_MONTHS (SYSDATE, 3) , 'Q' )'
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'
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
( 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));
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