Home » SQL & PL/SQL » SQL & PL/SQL » dbms_job submit - problem with date
dbms_job submit - problem with date [message #247236] Mon, 25 June 2007 03:39 Go to next message
dusoo
Messages: 41
Registered: March 2007
Member
Hi all, im trying to create a job that runs a procedure for last monday every monday ...
so i tried to run this query below but it did not work. Ended with "not a valid day of a week" error. Well, the problem is in the interval, but i dont know why. i have tried to change it to 'trunc(next_day(sysdate,1))' which runs selecting it from dual ... but no change.

thanks a lot

declare
job integer;
begin
dbms_job.submit(
job => job,
what => 'agr_spec_cases.day2week(trunc(sysdate-7));',
interval => 'trunc(NEXT_DAY(sysdate, ''MONDAY''))',
instance => 1);
commit;
end;
Re: dbms_job submit - problem with date [message #247238 is a reply to message #247236] Mon, 25 June 2007 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select NEXT_DAY(sysdate, 'MONDAY') from dual;
select NEXT_DAY(sysdate, 'MONDAY') from dual
                         *
ERROR at line 1:
ORA-01846: not a valid day of the week


SQL> alter session set nls_date_language=american;

Session altered.

SQL> select NEXT_DAY(sysdate, 'MONDAY') from dual;
NEXT_DAY(SYSDATE,'M
-------------------
02/07/2007 10:41:52

1 row selected.

Regards
Michel
Re: dbms_job submit - problem with date [message #247241 is a reply to message #247238] Mon, 25 June 2007 03:47 Go to previous message
dusoo
Messages: 41
Registered: March 2007
Member
thanks a lot, it worked ! Smile
Previous Topic: 360-day overview
Next Topic: help required in writing a query
Goto Forum:
  


Current Time: Thu Dec 08 18:27:10 CST 2016

Total time taken to generate the page: 0.08794 seconds