2. Define the Chain Steps

Now that I have created my chain, I can create my chain steps to call a program or another chain. In my case I will call a program. Each chain step calls a program that will call a procedure within my package – SCHEMA_REFRESH. Please note that the program_name in my chain steps are the same as the procedure names in my package, except the last chain step which will be handled differently. I do it this way as a way of self documentation. The program_name can be any name you choose. Listing 6 shows all of my chain steps.

BEGIN
    SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'REFRESH_TEST.REFRESH_PROC'
    ,step_name           => 'CLEANUP'
    ,program_name        => 'REFRESH_TEST.CLEANUP');
  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'REFRESH_TEST.REFRESH_PROC'
    ,step_name           => 'CLEAR_SCHEMA'
    ,program_name        => 'REFRESH_TEST.CLEAR_SCHEMA');
  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'REFRESH_TEST.REFRESH_PROC'
    ,step_name           => 'EXPORT_PROD'
    ,program_name        => 'REFRESH_TEST.EXPORT_PROD');
  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'REFRESH_TEST.REFRESH_PROC'
    ,step_name           => 'IMPORT_PROD'
    ,program_name        => 'REFRESH_TEST.IMPORT_PROD');
  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'REFRESH_TEST.REFRESH_PROC'
    ,step_name           => 'REMOVE_DUMP_FILE'
    ,program_name        => 'REFRESH_TEST.REMOVE_DUMPFILE');
  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'REFRESH_TEST.REFRESH_PROC'
    ,step_name           => 'SEND_EMAIL'
    ,program_name        => 'REFRESH_TEST.SEND_EMAIL');
END;

The named program or chain does not have to exist when defining the step. However it must exist and be enabled when the chain runs, otherwise an error is generated.