I want a job to run at 330 am on the 15th of every month. 1

[jwa6]

hello
I have a job I wouuld like to submit to delete historical data.
I need the job to run the 15th of every month at 330 am.

I am not sure how to get the date right ( IE the 15th)for the Interval

the DBMS_JOB.SUBMIT function.

DBMS_JOB.SUBMIT(
v_JobNum, -- Return of job number
v_proc -- procedure to run,
v_run_date, -- Next date/time to run
-- Interval if null dont run but once
v_run_date -- this will set the run to the 15 of each month


I did this block to test. I get an error as you can see below..

declare
v_date date;
--
begin
v_date := trunc(add_month(sysdate,1))+15­+(7/48);
dbms_output.put_line('v_date = '||to_char(v_date,'DD-MON-YYYY
HH24:MI:SS'));
'trunc(add_month(sysdate,1))+1­5+(7/48)'
end;


ORA-06550: line 5, column 17:
PLS-00201: identifier 'ADD_MONTH' must be declared
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

can anyone help?.....

jwa

 

[jwa6]

I got this to work
the add months depends on the day submitted. Using the last day plus 15 always hits the next month on the 15th
jim agans

declare
v_date date;
--
begin
v_date := LAST_DAY(TRUNC(sysdate)+(03+030/60)/24) + 15;
--v_date := trunc(add_months(sysdate,1))+15+(7/48);
dbms_output.put_line('v_date = '||to_char(v_date,'DD-MON-YYYY HH24:MI:SS'));
--'trunc(add_month(sysdate,1))+15+(7/48)'
end;

 

[sem]

I'd suggest you to use TRUNC(sysdate,'MM') instead of LAST_DAY as this seems to be more readable.

Regards, Dima

http://seminihin.narod.ru

 

[jwa6]

do you have an example?

this is all new to me sir

thanks!!