• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1611
  • Last Modified:

ORACLE DBMS JOB

This is with regards to the DBMS_JOB utility in ORACLE. I need to run a job at 10:00 AM on all days. The problem is that iam not able to specify the exact date as such. I can either set the next date to some currentdate+x number of hours/monutes/seconds. But ia not able to psefcify the exact date.

Let us assume that i need to run a job on
02-jan-2001 10:00AM  and my current system date is 01-jan-2001 18:00PM

My DBMS_JOB statement would like :

dbms_job.submit(:jnum,'xxx;',sysdate,'sysdate+(16/24)');

But say if i run this job at 20:00 Hrs on 01-jan-2001 the next date time will go further to 14:00 Hrs on 02-jan-2001.
How do i specify the exact time ?

Help me out

Very URGENT
0
vish_mp
Asked:
vish_mp
1 Solution
 
fvaCommented:
dbms_job.submit(:jnum,'xxx;','sysdate','trunc(sysdate)+10/24+1');
It worked for me.

Good luck,
F.
0
 
renurajCommented:
DBMS_JOB.SUBMIT (
   :job_no,
   <procedure_name>,
   sysdate,
   trunc(sysdate) + 10/24 + 1);

This will ensure the job ':job_no' to run everyday at 10:00 am.

Regards,
0
 
fvaCommented:
Well, renuraj, good comment, but you missed the quotes :) (both global " and local ' )

F.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
racherCommented:
-- this will submit the job for 10:00 tomorrow and will
-- rerun at 10:00 there after
DECLARE
  n_job_number     NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
    n_job_number
   ,'dp_package_name.procedure_name;'
   ,TO_DATE(TRUNC(SYSDATE+1)||' 10:00','dd-MON-yy hh24:mi')
   ,'TRUNC(SYSDATE+1) + 10/24'
   );
END;
/


0
 
fvaCommented:
Hi racher, welcome to ExEx.

The guidelines on comments and answers below and the ExEx-tiquette would have suggested that posting such an answer is not very appropriate. I agree that your proposed answer will work, but it's not that novel compared to the approaches proposed above to qualify for an answer (at least not according to what I've seen until now in ExEx Oracle thread).

Yours,
F.
0
 
racherCommented:
My mistake
0
 
fvaCommented:
As I said,

Welcome to ExEx.

Hope you'll be soon in top :)

F.
0
 
vish_mpAuthor Commented:
The solution given by racher sounds good but i had used the simpler one given by renuraj.

Thanks a lot
0
 
fvaCommented:
Hey, vish, my suggestion came within 8 minutes only after your request. You said it's URGENT, but you waited for renuraj's proposal (which is missing the same number of quotes as mine, BTW :) ). Your choice.
But why did you grade with B racher's solution if that was your choice?
Just wandering, 'cause it's still your choice. I don't even expect an answer.

Maybe you'll want to take a look at the recommendations about grading. racher's proposal works OK, solves the problem and contains the actual sample piece of code to solve it. What would a questioner want more? Why "punish" him with a B grade?

Yours,
F.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now