Home » SQL & PL/SQL » SQL & PL/SQL » Running a procedure on time
Running a procedure on time [message #219491] Wed, 14 February 2007 11:25 Go to next message
amardilo
Messages: 37
Registered: February 2007
Member
Hi - this is my first post here and I am a little lost with what to do.

I have a procedure I need to run on a certain day of the week at a certain time (the date and time will be stored in the db in a table).

Is this possible? If so has anyone got an example?
Re: Running a procedure on time [message #219499 is a reply to message #219491] Wed, 14 February 2007 11:43 Go to previous messageGo to next message
randerson@viops.com
Messages: 8
Registered: February 2007
Junior Member
Oracle has a built in scheduler. I did a Google search on
"scheduling oracle tasks" and got a bunch of hits. Here is a quicky explaination:

The Oracle Scheduler uses the supplied PL/SQL package DBMS_SCHEDULER to handle almost all scheduling Here is an example of scheduling a task with DBMS_JOB:

VARIABLE jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT (
job => :jobno
,what => 'BEGIN DBMS_STATS.
GATHER_SCHEMA_STATS(''HR'');END;'
,next_date => '09/01/2004 21:00:00'
,interval => 'TRUNC(SYSDATE) + 1 + 21/
24'
);
COMMIT;
END;

Thanks
Richard Anderson
Re: Running a procedure on time [message #219530 is a reply to message #219491] Wed, 14 February 2007 14:57 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
As Richard pointed out, if you are on 10g, you'll probably want to consider using the Scheduler over the older DBMS_JOB system (which is still available in 10g).

Check Moving to the Scheduler if on 10g and Using the power of DBMS_JOB.SUBMIT if you are on 10g and below.
Re: Running a procedure on time [message #219614 is a reply to message #219491] Thu, 15 February 2007 02:55 Go to previous message
amardilo
Messages: 37
Registered: February 2007
Member
Thanks guys.

I am using 1 10g database and 1 9i database (both requie the schudled tasks).
Previous Topic: tuining sqlquery
Next Topic: How to delete rows in column ?
Goto Forum:
  


Current Time: Thu Dec 08 20:34:43 CST 2016

Total time taken to generate the page: 0.10488 seconds