-- Create chain
BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name            =>  'my_chain',
   rule_set_name         =>  NULL,
   evaluation_interval   =>  NULL,
   comments              =>  NULL);
END;
/

-- Create 40 programs which contain the same procedure "proc" with different parameters and 1 program that analyze previously collected data (after proc(i) finished)
begin
    for i in 1 .. 40 loop
      --dbms_output.put_line(
      execute immediate
              'begin' || chr(10) ||
              'DBMS_SCHEDULER.CREATE_PROGRAM (' || chr(10) ||
              '   PROGRAM_NAME          => ''MY_PROGRAM'|| i||  chr(10) ||
              '   PROGRAM_TYPE          => ''PLSQL_BLOCK'',' || chr(10) ||
              '   PROGRAM_ACTION        => ''BEGIN proc(' || i ||
              '); END;'',' || chr(10) ||
              '   NUMBER_OF_ARGUMENTS   => 0,' || chr(10) ||
              '   ENABLED               => TRUE,' || chr(10) ||
              '   COMMENTS              => ''my_chain'');' || chr(10) ||
              'end;'
             ;
              --);
    end loop;
end;

begin 
DBMS_SCHEDULER.CREATE_PROGRAM (
   PROGRAM_NAME          => 'MY_PROGRAM_ANALYZE',
   PROGRAM_TYPE          => 'PLSQL_BLOCK',
   PROGRAM_ACTION        => 'BEGIN analyse_data(); END;',
   NUMBER_OF_ARGUMENTS   => 0,
   ENABLED               => TRUE,
   COMMENTS              => 'my_chain');
end;
/

-- Define 41 chain_steps accosiated with 41 programs created before 
begin
    for i in 1 .. 40 loop  
      execute immediate
      --dbms_output.put_line(
          'BEGIN' || chr(10) ||
          'DBMS_SCHEDULER.DEFINE_CHAIN_STEP(''my_chain'', ''step' || i || '' || ''', ''MY_PROGRAM' || i || ''')' || ';' || chr(10) ||
          'END;'/*)*/;
    end loop;  
end;

BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain', 'step_analize', 'MY_PROGRAM_ANALYZE');
END;
/

--- define corresponding rules for the chain.
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain', 'TRUE', 'START ' || 
'step1,step2,step3,step4,step5,step6,step7,step8,step9,step10,step11,step12,step13,step14, ' || 
'step15,step16,step17,step18,step19,step20,step21,step22,step23,step24,step25,step26,step27,step28, ' || 
'step29,step30,step31,step32,step33,step34,step35,step36,step37,step38,step39,step40');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain', 'step1 COMPLETED and step2 COMPLETED and step3 COMPLETED and step4 COMPLETED and step5 COMPLETED and step6 COMPLETED and step7 COMPLETED and ' || 
'step8 COMPLETED and step9 COMPLETED and step10 COMPLETED and step11 COMPLETED and step12 COMPLETED and step13 COMPLETED and step14 COMPLETED and ' || 
'step15 COMPLETED and step16 COMPLETED and step17 COMPLETED and step18 COMPLETED and step19 COMPLETED and step20 COMPLETED and step21 COMPLETED and ' || 
'step22 COMPLETED and step23 COMPLETED and step24 COMPLETED and step25 COMPLETED and step26 COMPLETED and step27 COMPLETED and step28 COMPLETED and ' || 
'step29 COMPLETED and step30 COMPLETED and step31 COMPLETED and step32 COMPLETED and step33 COMPLETED and step34 COMPLETED and step35 COMPLETED and ' || 
'step36 COMPLETED and step37 COMPLETED and step38 COMPLETED and step39 COMPLETED and step40 COMPLETED', 
'Start step_analize');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('my_chain', 'step_analize COMPLETED', 'END');
END;
/

--- enable the chain
BEGIN
  DBMS_SCHEDULER.ENABLE('my_chain');
END;
/

begin
  dbms_scheduler.run_chain(chain_name => 'my_chain');
end;

CREATE PROCEDURE salary ( deer NUMBER ) AS 
BEGIN UPDATE emp SET sal = sal - deer; 
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM
( program_name        => 'simple_program1'
, program_type        => 'STORED_PROCEDURE'
, program_action      => 'salary'
, enabled             => FALSE
, number_of_arguments => 1 
) ;
END;
/

CREATE TABLE CHAIN_TEST (
       i number,
       status char(1),
       dt date,
       session_id number
);
/       

--select * from CHAIN_TEST order by dt,i, status
--truncate table CHAIN_TEST

CREATE OR REPLACE PROCEDURE proc(i NUMBER) as
BEGIN
  insert into CHAIN_TEST
  VALUES (i, 'B', sysdate, SYS_CONTEXT('userenv','SESSIONID'));
  commit;
  
  dbms_lock.sleep(i);
  insert into CHAIN_TEST
  VALUES (i, 'E', sysdate, SYS_CONTEXT('userenv','SESSIONID'));
  commit;
END;
/


CREATE OR REPLACE PROCEDURE analyse_data as
BEGIN
  proc(20);
END;
)

------------/*1. Global context variable*/------------
CREATE OR REPLACE CONTEXT globalcontext
USING globalcontext_pkg
ACCESSED GLOBALLY
/

CREATE OR REPLACE PACKAGE globalcontext_pkg AS
       PROCEDURE set_value (
         par varchar2
       , val varchar2
       );
END;
/

CREATE OR REPLACE PACKAGE BODY globalcontext_pkg AS
       PROCEDURE set_value (
         par varchar2
       , val varchar2
       ) 
       AS
       BEGIN
         DBMS_SESSION.SET_CONTEXT (
            'globalcontext'
          , par
          , val
         );
       END; 
END;
/

--select * from all_objects where = 'globalcontext_pkg'
------------/*2. Create chain*/------------
BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name            =>  'my_chain',
   rule_set_name         =>  NULL,
   evaluation_interval   =>  NULL,
   comments              =>  NULL);
END;
/

------------/*3. Define parameters (global context variables)*/------------
begin 
DBMS_SCHEDULER.CREATE_PROGRAM (
   PROGRAM_NAME          => 'PROGRAM_SET_PAR',
   PROGRAM_TYPE          => 'PLSQL_BLOCK',
   PROGRAM_ACTION        => 'begin' || chr(10) ||
                            '  for i in 1 .. 10 loop' || chr(10) ||
                            '     globalcontext_pkg.set_value (''PROC_PAR''||i, to_char(i));' || chr(10) ||
                            '  end loop;' || chr(10) ||
                            'end;',
   NUMBER_OF_ARGUMENTS   => 0,
   ENABLED               => TRUE,
   COMMENTS              => 'DEFINE_PARAMETERS');
end;
/

------------/*4. Define 10 PARALLEL_PROGRAM*/------------

begin
    for i in 1 .. 10 loop
      --dbms_output.put_line(
      execute immediate
              'begin' || chr(10) ||
              'DBMS_SCHEDULER.CREATE_PROGRAM (' || chr(10) ||
              '   PROGRAM_NAME          => ''PARALLEL_PROGRAM'|| i ||''',' || chr(10) ||
              '   PROGRAM_TYPE          => ''PLSQL_BLOCK'',' || chr(10) ||
              '   PROGRAM_ACTION        => ''BEGIN proc(SYS_CONTEXT(''''globalcontext'''',''''PROC_PAR'|| i||''''')' ||
              '); END;'',' || chr(10) ||
              '   NUMBER_OF_ARGUMENTS   => 0,' || chr(10) ||
              '   ENABLED               => TRUE,' || chr(10) ||
              '   COMMENTS              => ''PARALLEL_PROCEDURE'||i||''');' || chr(10) ||
              'end;'
              ;
              --);
    end loop;
end;

------------/*5. Define BLOCK after PARALLEL execution*/------------
begin 
DBMS_SCHEDULER.CREATE_PROGRAM (
   PROGRAM_NAME          => 'PROGRAM_FINAL',
   PROGRAM_TYPE          => 'PLSQL_BLOCK',
   PROGRAM_ACTION        => 'BEGIN analyse_data(); END;',
   NUMBER_OF_ARGUMENTS   => 0,
   ENABLED               => TRUE,
   COMMENTS              => 'BLOCK_AFTER_PARALLEL_EXECUTION');
end;
/

------------/*6. Define steps*/------------
BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain', 'step0', 'PROGRAM_SET_PAR');
END;
/


begin
    for i in 1 .. 10 loop  
      execute immediate
      --dbms_output.put_line(
          'BEGIN' || chr(10) ||
          'DBMS_SCHEDULER.DEFINE_CHAIN_STEP(''my_chain'', ''step' || i ||'' || ''', ''PARALLEL_PROGRAM' || i || ''')' || ';' || chr(10) ||
          'END;';
          --);
    end loop;
end;


BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain', 'final_step', 'PROGRAM_FINAL');
END;
/

------------/*7. Define rules*/------------
BEGIN
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain', 'TRUE', 'START step0');
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE
           ('my_chain', 'step0 COMPLETED', 
            'START step1,step2,step3,step4,step5,step6,step7,step8,step9,step10');
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
            'my_chain', 'step1 COMPLETED and step2 COMPLETED and step3 COMPLETED and ' || 
            'step4 COMPLETED and step5 COMPLETED and step6 COMPLETED and step7 COMPLETED and ' || 
            'step8 COMPLETED and step9 COMPLETED and step10 COMPLETED', 
            'Start final_step');
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('my_chain', 'final_step COMPLETED', 'END');
END;
/

------------/*8. Enable the chain*/------------
BEGIN
  DBMS_SCHEDULER.ENABLE('my_chain');
END;
/

------------/*9. Run chain*/------------
begin
  dbms_scheduler.run_chain(chain_name => 'my_chain', start_steps => 'step0');
end;