dbms_job.interval [message #285459] |
Tue, 04 December 2007 14:21 |
nathan0506
Messages: 2 Registered: December 2007
|
Junior Member |
|
|
Hi, im trying to change interval of a job but i get the following error:
SQL> begin
2 DBMS_JOB.INTERVAL(137,'TO_DATE(TO_CHAR((SYSDATE+1),'DD/MM/YYYY') || ' 23:50','DD/MM/YYYY HH24:MI')');
3 end
4 /
SP2-0552: Bind variable "MI" not declared.
Im trying to submit job to execute everyday at 23:50. The expression works fine with sys.dual:
SQL> select TO_DATE(TO_CHAR((SYSDATE+1),'DD/MM/YYYY')||' 23:50','DD/MM/YYYY HH24:MI') from sys.dual;
TO_DATE(T
---------
05-DEC-07
Or more specifically:
SQL> select to_char(TO_DATE(TO_CHAR((SYSDATE+1),'DD/MM/YYYY')||' 23:50','DD/MM/YYYY HH24:MI'),'dd-mm-yy hh24:mi') fr
om sys.dual;
TO_CHAR(TO_DAT
--------------
05-12-07 23:50
Why do i get this error?
Thanks
Nathan
|
|
|
Re: dbms_job.interval [message #285462 is a reply to message #285459] |
Tue, 04 December 2007 14:42 |
|
Michel Cadot
Messages: 68712 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
You have to double the ' inside the string.
'TO_DATE(TO_CHAR((SYSDATE+1),''DD/MM/YYYY'') || '' 23:50'',''DD/MM/YYYY HH24:MI'')'
But you can also use 'trunc(sysdate)+2-10/1440' for the same result.
Regards
Michel
[Updated on: Tue, 04 December 2007 14:44] Report message to a moderator
|
|
|
|
Re: dbms_job.interval [message #285466 is a reply to message #285465] |
Tue, 04 December 2007 14:53 |
|
Michel Cadot
Messages: 68712 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Well, for a logical point of view, you work on date, so use date arithmetic and not string manipulations.
When you convert into/from strings you have to make (implicit) assumptions like calendar that is used.
Regards
Michel
|
|
|