codemonth.dk

One project every month - making stuff better ...

PLSQL promises vs dbms_scheduler chains

10 lines of code vs 80 lines of code! When implementing JS promises in PLSQL, one of the main use cases is the ability that promises gives, to easily chain together dependent steps defined as functions. Also the ability to easily pass output from one step along to the next step in the chain.

But hey Morten, isn't this just the same as regular DBMS_SCHEDULER chain job then? Well, sort of, kind of and not really. What we cannot do with a regular job chain, is take an already executed job object and reuse it again in a new chain of dependencies while still keeping state in the running process. So when you build a job chain using dbms_scheduler you build the entire chain at that point and then you execute it as a normal job. Second there is no easy way to move back in time and inject extra steps or re-arrange the chain in a running process. Promises allows me to do this, very easily as you will be able to see in the below examples. The amount of code should be enough to convince you on the usefullness of the promise library.

Allow me to give you an example of both, to show the flexibility of promises over normal jobs. First we will create a standard dbms_scheduler chain, execute it and then after full run of the chain, we will pretend we need to add an extra step. After that I will do the same using promises.

To make this comparison even more fair, the code that we will use for the dbms_scheduler chain will not have any output parameters, because there is no easy way of passing output parameters between scheduler steps. In fact it is still not supported to add an output argument to a scheduler program.

What follows is first the code to setup the dbms_scheduler chain for this. Apart from the create job privilege, the user also needs specific administration privileges for the DBMS_RULE_ADM package. These grants needs to be executed with system privileges:


begin
  dbms_rule_adm.grant_system_privilege (
    privilege       =>    dbms_rule_adm.create_rule_set_obj
    , grantee       =>    'PROMISES_NINJA'
    , grant_option  =>    false
  );

  dbms_rule_adm.grant_system_privilege (
    privilege       =>    dbms_rule_adm.create_evaluation_contex_obj
    , grantee       =>    'PROMISES_NINJA'
    , grant_option  =>    false
  );

  dbms_rule_adm.grant_system_privilege (
    privilege       =>    dbms_rule_adm.create_rule_obj
    , grantee       =>    'PROMISES_NINJA'
    , grant_option  =>    false
  );
end;
/

So after we have the necesarry privileges we need to build the components to be able to run the chain. Below is the complete code to build all the required scheduler objects:

  • Scheduler program
  • Chain
  • Chain steps
  • Chain rules


begin

  -- Create the scheduler objects
  dbms_scheduler.create_program (
    program_name          =>    'SC_PRG_1'
    , program_type        =>    'PLSQL_BLOCK'
    , program_action      =>    'begin
        insert into promise_runs values (sysdate, ''SC_PRG_1 was here'');
        commit;
      end;'
    , comments            =>    'PRG 1'
  );

  dbms_scheduler.create_program (
    program_name          =>    'SC_PRG_2'
    , program_type        =>    'PLSQL_BLOCK'
    , program_action      =>    'begin
        insert into promise_runs values (sysdate, ''SC_PRG_2 was here'');
        commit;
      end;'
    , comments            =>    'PRG 2'
  );

  dbms_scheduler.create_program (
    program_name          =>    'SC_PRG_3'
    , program_type        =>    'PLSQL_BLOCK'
    , program_action      =>    'begin
        insert into promise_runs values (sysdate, ''SC_PRG_3 was here'');
        commit;
      end;'
    , comments            =>    'PRG 3'
  );

  -- Create the chain itself.
  dbms_scheduler.create_chain (
    chain_name            =>    'SCH_VS_PROM_CHAIN'
    , rule_set_name       =>    null
    , evaluation_interval =>    null
    , comments            =>    'The chain.'
  );

  -- Chain steps
  dbms_scheduler.define_chain_step (
    chain_name            =>    'SCH_VS_PROM_CHAIN'
    , step_name           =>    'CHAIN_STEP_1'
    , program_name        =>    'SC_PRG_1'
  );

  dbms_scheduler.define_chain_step (
    chain_name            =>    'SCH_VS_PROM_CHAIN'
    , step_name           =>    'CHAIN_STEP_2'
    , program_name        =>    'SC_PRG_2'
  );

  dbms_scheduler.define_chain_step (
    chain_name            =>    'SCH_VS_PROM_CHAIN'
    , step_name           =>    'CHAIN_STEP_3'
    , program_name        =>    'SC_PRG_3'
  );

  -- Create the chain step rules
  dbms_scheduler.define_chain_rule (
    chain_name            =>    'SCH_VS_PROM_CHAIN'
    , condition           =>    'TRUE'
    , action              =>    'START "CHAIN_STEP_1"'
    , rule_name           =>    'chain_rule_1'
    , comments            =>    'First rule evaluation.'
  );

  dbms_scheduler.define_chain_rule (
    chain_name            =>    'SCH_VS_PROM_CHAIN'
    , condition           =>    '"CHAIN_STEP_1" COMPLETED'
    , action              =>    'START "CHAIN_STEP_2"'
    , rule_name           =>    'chain_rule_2'
    , comments            =>    'Second rule evaluation.'
  );

  dbms_scheduler.define_chain_rule (
    chain_name            =>    'SCH_VS_PROM_CHAIN'
    , condition           =>    '"CHAIN_STEP_2" COMPLETED'
    , action              =>    'START "CHAIN_STEP_3"'
    , rule_name           =>    'chain_rule_3'
    , comments            =>    'Third rule evaluation.'
  );

  dbms_scheduler.define_chain_rule (
    chain_name            =>    'SCH_VS_PROM_CHAIN'
    , condition           =>    '"CHAIN_STEP_3" COMPLETED'
    , action              =>    'END'
    , rule_name           =>    'chain_rule_4'
    , comments            =>    'Fourth rule evaluation.'
  );

  -- Enable the chain
  dbms_scheduler.enable('SCH_VS_PROM_CHAIN');

end;
/

Phew, that is quite a lot of code; And we haven't even run the chain yet. To do that we need to define a job and use the chain for that job:


begin

  dbms_scheduler.create_job (
    job_name              =>    'RUN_SCH_CHAIN_J'
    , job_type            =>    'CHAIN'
    , job_action          =>    'SCH_VS_PROM_CHAIN'
    , start_date          =>    systimestamp
    , enabled             =>    true
  );

end;
/

If we now check the table, we can see the job has created 3 rows.


SQL> column run_result format a50
SQL> select * from promise_runs;

RUN_FINISHED       RUN_RESULT
------------------ --------------------------------------------------
06-MAY-17 12:45:10 SC_PRG_1 was here
06-MAY-17 12:45:11 SC_PRG_2 was here
06-MAY-17 12:45:11 SC_PRG_3 was here

SQL>

That was the complete dbms_scheduler code for a chained run. Now let us look at how that would look using the PROMISE_NINJA library. For this library we will use the same functions as we have used in a previous promises_ninja demo and they can be taken from this Github GIST.

Below is the promises code that does the exact same thing as the above scheduler code:


declare
  test_promise promise;
begin
  test_promise := promise('p_multiplier', 42);
  test_promise := test_promise.then_f('p_multi_convert');
  test_promise := test_promise.then_f('p_end_run');
  promises_ninja.promise_wait(test_promise);
end;
/

And if we select from the table again, we can see the complete chain completed for the promise code:


SQL> column run_result format a50
SQL> select * from promise_runs;

RUN_FINISHED       RUN_RESULT
------------------ --------------------------------------------------
06-MAY-17 12:45:10 SC_PRG_1 was here
06-MAY-17 12:45:11 SC_PRG_2 was here
06-MAY-17 12:45:11 SC_PRG_3 was here
06-MAY-17 12:57:34 yes more than 50

SQL>

Now imagine that we wanted to add an extra step to the chain. For the scheduler we will have to

  • Add the program to be executed in the step
  • Disable the chain (which would disable dependencies)
  • Drop the last chain rule
  • Define the new chain step
  • Add the new chain rule.
  • Recreate the final chain step again to end the chain
  • Enable the chain
So the code for that would be


begin

  dbms_scheduler.create_program (
    program_name          =>    'SC_PRG_4'
    , program_type        =>    'PLSQL_BLOCK'
    , program_action      =>    'begin
        insert into promise_runs values (sysdate, ''SC_PRG_4 was here'');
        commit;
      end;'
    , comments            =>    'PRG 4'
  );

  dbms_scheduler.disable('SCH_VS_PROM_CHAIN');

  dbms_scheduler.drop_chain_rule (
    chain_name            =>    'SCH_VS_PROM_CHAIN'
    , rule_name           =>    'chain_rule_4'
    , force               =>    true
  );

  dbms_scheduler.define_chain_step (
    chain_name            =>    'SCH_VS_PROM_CHAIN'
    , step_name           =>    'CHAIN_STEP_4'
    , program_name        =>    'SC_PRG_4'
  );

  dbms_scheduler.define_chain_rule (
    chain_name            =>    'SCH_VS_PROM_CHAIN'
    , condition           =>    '"CHAIN_STEP_3" COMPLETED'
    , action              =>    'START "CHAIN_STEP_4"'
    , rule_name           =>    'chain_rule_4'
    , comments            =>    'Fourth rule evaluation.'
  );

  dbms_scheduler.define_chain_rule (
    chain_name            =>    'SCH_VS_PROM_CHAIN'
    , condition           =>    '"CHAIN_STEP_4" COMPLETED'
    , action              =>    'END'
    , rule_name           =>    'chain_rule_5'
    , comments            =>    'Fifth rule evaluation.'
  );

  -- Enable the chain
  dbms_scehduler.enable('SCH_VS_PROM_CHAIN');

end;
/

Again, quite a lot of code for something relatively simple. If we work with the promises_ninja library it is a lot more easier than that. Simple add an extra [then_f] call.


declare
  test_promise promise;
begin
  test_promise := promise('p_multiplier', 42);
  test_promise := test_promise.then_f('p_multi_convert');
  -- NEW EXTRA STEP
  test_promise := test_promise.then_f('p_new_step');
  test_promise := test_promise.then_f('p_end_run');
  promises_ninja.promise_wait(test_promise);
end;
/

Again quite simple, very intuitive way of running things in sequence. In fact to be honest: All promises_ninja is, is a more easy to use abstraction on top of dbms_scheduler (and a bit more control magic using advanced queueing and the anydata data type). So you get the reliability of dbms_scheduler and the flexibility of a the javascript functionality of the promise.

Tagged in : Advanced Queuing, DBMS_SCHEDULER, Object types, PL/SQL, sys.anydata