Re: ?: running procedure at predefine time

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 12 May 1998 11:34:04 GMT
Message-ID: <355e339b.10705583_at_192.86.155.100>

A copy of this was sent to "Danil Karsnov" <danil_at_maginfo.net> (if that email address didn't require changing) On 12 May 1998 11:34:26 GMT, you wrote:

>
>Hello, all!
>
>I need to run my PL/SQL procedure once a day...
>
>Whats the way to do that?
>
>My Oracle runs under NT, and possile to use command AT.
>Is it possible to run SQLplus with sql-file to execute?
>Also, I know that there is package for running procedures at
>predefined time, but never used it..
>
>Will be thankful for any advices.
>
>Danil Krasnov.
>danil_at_maginfo.net

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;
end;
/

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  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

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 - 13:34:04 CEST