Solved

ORACLE DBMS JOB

Posted on 2001-06-26
9
1,593 Views
Last Modified: 2012-05-04
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
Comment
Question by:vish_mp
9 Comments
 
LVL 4

Expert Comment

by:fva
ID: 6226685
dbms_job.submit(:jnum,'xxx;','sysdate','trunc(sysdate)+10/24+1');
It worked for me.

Good luck,
F.
0
 
LVL 2

Expert Comment

by:renuraj
ID: 6226926
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
 
LVL 4

Expert Comment

by:fva
ID: 6226974
Well, renuraj, good comment, but you missed the quotes :) (both global " and local ' )

F.
0
 
LVL 2

Accepted Solution

by:
racher earned 200 total points
ID: 6227008
-- 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:fva
ID: 6227042
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
 
LVL 2

Expert Comment

by:racher
ID: 6227195
My mistake
0
 
LVL 4

Expert Comment

by:fva
ID: 6227550
As I said,

Welcome to ExEx.

Hope you'll be soon in top :)

F.
0
 

Author Comment

by:vish_mp
ID: 6236788
The solution given by racher sounds good but i had used the simpler one given by renuraj.

Thanks a lot
0
 
LVL 4

Expert Comment

by:fva
ID: 6237737
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Format Number Field 10 40
Oracle Finace 3 48
Out of Sequence numbers for today 25 47
compre toata in where clue oracle 4 44
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now