Interval in DBMS_JOB [message #656814] |
Wed, 19 October 2016 15:45 |
azeem87
Messages: 116 Registered: September 2005 Location: dallas
|
Senior Member |
|
|
Hi,
try to make this dbms job first run from 11/6 and then after that
on First sunday of the month.
DECLARE
X NUMBER;
user_name varchar2(30);
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'DROP_HISTORICAL_PARTITIONS(''MD_LOG_PARTITION'',9);'
,next_date => to_date('05/11/2016 12:00:01','dd/mm/yyyy hh24:mi:ss')
,interval => next_day(last_day(sysdate),'SUN')
,no_parse => FALSE
);
COMMIT;
END;
/
getting this error.
Error at line 1
ORA-23319: parameter value "06-NOV-16" is not appropriate
ORA-06512: at "SYS.DBMS_JOB", line 60
ORA-06512: at "SYS.DBMS_JOB", line 139
ORA-06512: at line 5
is this because in interval it's sysdate , or what's wrong please advise.
Thanks
|
|
|
|
Re: Interval in DBMS_JOB [message #656831 is a reply to message #656816] |
Thu, 20 October 2016 04:18 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
To be clear - interval needs to passed a string that contains a function that will evaluate to the next date the job should be run.
You've passed it an actual function and so oracle does what it always does - evaluate the function before calling the procedure and pass the result of the function as the parameter.
So you've passed it a date.
|
|
|