My Oracle Support Banner

Sample How to Create a Job Running Chains (Doc ID 727499.1)

Last updated on MARCH 13, 2018

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 10.2 to 11.2]
Oracle Database - Enterprise Edition - Version to [Release 10.2 to 11.2]
Oracle Database - Enterprise Edition - Version to [Release 12.1]
Information in this document applies to any platform.
Oracle version 11.2


This note will show an example of how a job using chains can be created and automatically restarted:


-- drop and create a testuser and the needed privileges
conn / as sysdba
drop user crc cascade;
create user crc identified by crc;
grant connect, resource to crc;

grant create any job to crc;
grant execute on dbms_scheduler to crc;



-- connect as user crc
connect crc/crc
alter session set nls_language = american;
alter session set nls_date_format= 'DD-MM-YY HH24:MI:SS';

create table chaintest ( a date, b number);
-- create two programs

exec DBMS_SCHEDULER.CREATE_PROGRAM ( program_name=>  'my_program1', program_type=>'PLSQL_BLOCK', program_action=>  'BEGIN insert into chaintest values (sysdate , 1); commit; END;' ,enabled=>TRUE);

exec DBMS_SCHEDULER.CREATE_PROGRAM ( program_name=>  'my_program2', program_type=>'PLSQL_BLOCK', program_action=>  'BEGIN insert into chaintest values (sysdate , 2); commit; END;' ,enabled=>TRUE);

-- create the chain 

exec DBMS_SCHEDULER.CREATE_CHAIN ( chain_name =>'my_chain');

--- define corresponding rules and the steps for the chain.

exec DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain', 'TRUE', 'START stepA','chain_rule1',NULL);
exec DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain', 'stepA', 'my_program1');

exec DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('my_chain', 'stepA COMPLETED', 'START stepB','chain_rule2',NULL);

exec DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain', 'stepB', 'my_program2');
exec DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain', 'stepB COMPLETED','END', 'chain_rule3',NULL);

-- enable the chain
exec DBMS_SCHEDULER.ENABLE ('my_chain');

--- create a job with included schedule

    DBMS_SCHEDULER.create_job (
    job_name => 'runchain_job',
    job_type => 'CHAIN',
    job_action => 'my_chain',
    start_date => SYSDATE,
    repeat_interval => 'SYSDATE + 3/1440' ,
    enabled => TRUE);


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.