Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Scheduling a PL/SQL Program?
A copy of this was sent to mark_tortolano_at_youre.welcome.com (Mark Tortolano)
(if that email address didn't require changing)
On Tue, 12 May 1998 10:13:03 GMT, you wrote:
>
>I have a PL/SQL block that I need to execute after midnight every
>night to update the content of a table. Should I embed this as a
>subprogram directly into the database (I currently run it from
>SQL*Plus, but have problems getting it to run as a cron job)? If I do
>that, how do I trigger it at a given time each day?
>
>Thanks,
>
>Mark Tortolano
Use dbms_jobs.
Make sure you set the init.ora parameters:
job_queue_interval= 60
job_queue_processes = 1
the first one checks how often the queue is checked (every 60 seconds with the above) and how many queues there are defined (how many submitted blocks can execute concurrently -- 1 in the above).
Then, you can use dbms_job.submit to submit and schedule the job. For example, to get something to run everyday at midnight you would:
declare
l_job number;
begin
dbms_job.submit( job => l_job,
what => 'YOUR_CODE_HERE;', next_date => trunc(sysdate)+1, interval => 'trunc(sysdate)+1' );commit;
That will schedule your job to initially run at midnight today and then every subsequent midnight.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue May 12 1998 - 06:30:59 CDT