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 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]
Oracle Database - Enterprise Edition - Version 10.2.0.5 to 11.2.0.3 [Release 10.2 to 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.
Oracle version 10.2.to 11.2


Goal

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;

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_ANY_RULE, 'CRC');
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (DBMS_RULE_ADM.CREATE_ANY_RULE_SET, 'CRC');
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (DBMS_RULE_ADM.CREATE_ANY_EVALUATION_CONTEXT, 'CRC');
END;
/

execute DBMS_SCHEDULER.PURGE_LOG;

-- 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

    BEGIN
    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);
    End;
/

Solution

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.