Sunday, August 24, 2014

Schedule Oracle Procedure

We may want to run our Oracle procedures on a regular basis. For example, every day at 10PM, I want to get new transactions from a database schema, calculate the risk score and insert the result into a table. I first created a procedure, proc_all, and then I use DBMS_SCHEDULER.create_job() to schedule the job. This procedure will run automatically every day at 10pm.

  DBMS_SCHEDULER.create_job (
    job_name        => 'my_proc',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN proc_all; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=22;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');
If we want to stop the job, we can drop it using DBMS_SCHEDULER.drop_job()
begin DBMS_SCHEDULER.drop_job(job_name => 'my_proc'); end;
We can query views DBA_SCHEDULER_JOBS or USER_SCHEDULER_JOBS to get the status of scheduled jobs.

JOB_NAME                         JOB_ACTION
-------------------------- ------------------------------------------------------------------
MY_PROC                   BEGIN proc_all; END;
PROC_CLEAR_TBL            BEGIN execute immediate 'truncate table tbl_tobe_alerted'; END;

No comments: