Home » SQL & PL/SQL » SQL & PL/SQL » dbms_job.interval
dbms_job.interval [message #285459] Tue, 04 December 2007 14:21 Go to next message
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 Go to previous messageGo to next message
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 #285465 is a reply to message #285462] Tue, 04 December 2007 14:49 Go to previous messageGo to next message
nathan0506
Messages: 2
Registered: December 2007
Junior Member
Thanks. Both ways go fine.
Re: dbms_job.interval [message #285466 is a reply to message #285465] Tue, 04 December 2007 14:53 Go to previous message
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
Previous Topic: where current of use
Next Topic: trigge on server erro
Goto Forum:
  


Current Time: Tue Nov 12 22:25:49 CST 2024