Home » SQL & PL/SQL » SQL & PL/SQL » How can I delay a dbms_job?
How can I delay a dbms_job? [message #205022] Thu, 23 November 2006 01:37 Go to next message
ndahafa
Messages: 16
Registered: November 2006
Junior Member
Hi,
I have a procedure that I want to run from today at 12 p.m and at 3 a.m every day thereafter.

I need to know how to delay the job so that it doesn't run immediately after submitting it. i.e. what should I use instead of sysdate to delay it till 12 p.m today?

here's my code:

declare
jobno number;
begin
dbms_job.submit( job => jobno,
what => 'begin procedure_name_here;end;',
next_date => sysdate,
interval => 'trunc(sysdate + 1) + 3/24');
commit;
dbms_output.put_line( ' your job is # '|| jobno);
end;

thanks in advance
Re: How can I delay a dbms_job? [message #205027 is a reply to message #205022] Thu, 23 November 2006 01:49 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Not tested....
You can use
next_date => trunc(sysdate)+12

By
Vamsi
Re: How can I delay a dbms_job? [message #205028 is a reply to message #205022] Thu, 23 November 2006 01:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Please do not report your own messages to the moderators in an attempt to get a reply.
Re: How can I delay a dbms_job? [message #205055 is a reply to message #205028] Thu, 23 November 2006 02:57 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
It should be
next_date => trunc(sysdate)+12/24
Re: How can I delay a dbms_job? [message #205059 is a reply to message #205055] Thu, 23 November 2006 03:03 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Oooooops Cool
How did I miss that?

By
Vamsi
Re: How do I find out why a job failed? [message #205079 is a reply to message #205055] Thu, 23 November 2006 04:35 Go to previous messageGo to next message
ndahafa
Messages: 16
Registered: November 2006
Junior Member
next_date => trunc(sysdate)+12/24

this worked, thanks.

however, my job failed. shows 4 failures since 12 p.m. how do I find out why a job failed? I know my procedure is running fine because when I call it without using dbms_job it runs successfully.
Is there an error log or something like that for scheduled jobs?
Re: How do I find out why a job failed? [message #205082 is a reply to message #205079] Thu, 23 November 2006 04:47 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Look in the Alert Log. That should show you the error message raised for each failure.
Previous Topic: how to code this in PLSQL(dynamic) (merged 3 cross-posts)
Next Topic: grant inside trigger
Goto Forum:
  


Current Time: Fri Dec 09 03:41:38 CST 2016

Total time taken to generate the page: 0.06972 seconds