Run oracle procedure automatically every hour

oracle, plsql, procedure

Solution

Use `DBMS_SCHEDULER` and create a job similar to this:

begin

    DBMS_SCHEDULER.CREATE_JOB (
         job_name           => 'CALC_JOB',
         job_type           => 'STORED_PROCEDURE',
         job_action         => 'UPDATE_SUMMARY',
         start_date         => current_timestamp,
         repeat_interval    => 'FREQ=hourly;',
         enabled            => true);

end;
/

More details (especially about the `repeat_interval` parameter in the manual: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHEHDHA

The `begin .. end` is only necessary because SQL*PLus does not allow the `execute` command to span multiple line. If you are using a different SQL client you might not need this.

I usually also set the logging level for the job in order to see more information in `USER_JOB_RUN_DETAIL`

execute dbms_scheduler.set_attribute(name => 'CALC_JOB', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FULL);

Problem

i have created a simple procedure in oracle. pseudo code is ``` CREATE OR REPLACE procedure update_summary begin delete from summary table; make different calculations from different tables, insert values row by row in summary table; end; ``` I want this procedure to run automatically every hour since if i call it from front end of my application, its quiet time-hungry and user think that page has hanged. help would be highly appreciated.

Original source