Home » SQL & PL/SQL » SQL & PL/SQL » Interval in DBMS_JOB (11.2.0.4)
Interval in DBMS_JOB [message #656814] Wed, 19 October 2016 15:45 Go to next message
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 #656816 is a reply to message #656814] Wed, 19 October 2016 20:08 Go to previous messageGo to next message
warmbreeze
Messages: 1
Registered: October 2016
Junior Member
interval IN VARCHAR2 DEFAULT 'null',

so, correct format:
interval => q'[next_day(last_day(sysdate),'SUN')]'
Re: Interval in DBMS_JOB [message #656831 is a reply to message #656816] Thu, 20 October 2016 04:18 Go to previous message
cookiemonster
Messages: 13920
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.
Previous Topic: sql query with combination of row values
Next Topic: multi insert
Goto Forum:
  


Current Time: Thu Apr 25 12:10:09 CDT 2024