Monday, July 23, 2012

DBMS_SCHEDULER quick tutorial

http://www.oracle-base.com/articles/10g/scheduler-10g.php

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm#ARPLS138

Putting into action: let's create a job running every minute to delete all records from the table PIPPO:


CREATE TABLE "PIPPO" ("NAME" VARCHAR2(20 BYTE)) ;

insert into PIPPO values ('A');
insert into PIPPO values ('B');
commit();



then we create a stored procedure to delete records:

create or replace
PROCEDURE MY_JOB_PROCEDURE AS
BEGIN
delete from PIPPO;
END MY_JOB_PROCEDURE;



then I create the job:

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_full_job_definition',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN my_job_procedure; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=MINUTELY; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END;
/


No comments: