Controling ORACLE jobs with a simple button click

I found that users have issues with a status driven environment where ORACLE jobs complete part of a proces. They want to manage (stop, start, kick) the job but lack the technical knowhow (or even authorisation). Each time they will have to ask the developers for help with some minor PL/SQL scripting.

So I made an application where users could stop, start, or kick a job into action with a mere click on a button. This would help them to control the proces better.

Here is a screenprint of what the application ended up like:

What you see above is a screen with all the specific jobs you wish your administrators to control.  The administrators can just push the buttons to control the jobs.

The Code

Here I will explain what code was needed to make it all work.  The data is based on a view you create on the table USER_SCHEDULED_JOBS.

create view mv_v_scheduler_jobs as (
 select job_name, enabled, repeat_interval, last_start_date, next_run_date, comments , job_action, start_date, job_type
  from user_SCHEDULER_JOBS
  WHERE job_name in ( 'PHT_VERWERK_ZAAK_STATUS_104', 'PHT_VERWERK_ZAAK_STATUS_111'                    , 'PHT_VERWERK_ZAAK_STATUS_197', etc etc );

Create an interactive report with the following query:

select job_name
         , enabled 
         , repeat_interval
         , last_start_date
         , next_run_date
         , comments 
         , null as button1
         , null as button2
         , null as button3
from mv_v_ scheduler_jobs

On page level add these Inline CSS, these are the red X and green check icons. The column “ENABLED” of this view is a field containing the text values TRUE or FALSE.

.syn-check-TRUE:before {
  content: "\f00c";
  color: green;
}

.syn-check-FALSE:before {
  content: "\f00d";
  color: #ff3a30;
}

These are the ‘special’ columns specified in the above interactive report.

Column: ENABLED
Type: Plain Tekst
Heading: Enabled
Column Formatting, HTML Expression: <span class="fa  syn-check-#ENABLED#" alt="#ENABLED#"></span>

Column: BUTTON1
Type: Link
Heading: Enable
Link Target: 3000 ( back to your own page ), Request: E_#JOB_NAME#
Link Tekst: Enable
Link Attributes: class="t-Button t-Button--simple t-Button--hot t-Button--stretch"

Column: BUTTON2
Type: Link
Heading: Disable
Link Target: 3000 ( back to your own page ), Request: D_#JOB_NAME#
Link Tekst: Disable
Link Attributes: class="t-Button t-Button--simple t-Button--hot t-Button--stretch"

Column: BUTTON3
Type: Link
Heading: Run job
Link Target: 3000 ( back to your own page ), Request: R_#JOB_NAME#
Link Tekst: run job
Link Attributes: class="t-Button t-Button--simple t-Button--hot t-Button--stretch"

Create a hidden Item field named: “P3000_MESSAGE” to contain the message.
Create the following PL/SQL code on page level at “Before header”

DECLARE
  l_request VARCHAR2(50) := :REQUEST;
  l_todo    VARCHAR2(2)  := SUBSTR(l_request, 1, 2);
  l_job     VARCHAR2(50) := SUBSTR(l_request, 3);
  l_message varchar2(500) := '';
BEGIN
  -- Request is like E_ D_ of R_<JOBNAME>
    IF l_todo = 'E_'
    THEN
      mv_util_pck.enable_job (l_job);
      l_message := l_job|| ' is now enabled.';
    ELSIF l_todo = 'D_'
    THEN
      mv_util_pck.disable_job (l_job);
      l_message := l_job|| ' is now disabled.';
    ELSIF l_todo = 'R_'
    THEN
      mv_util_pck.run_job (l_job);
      l_message := l_job|| ' is now started.';
    END IF;  
    :P3000_MESSAGE := l_message;
END;

Create the followng PL/SQL procedure in your package

  procedure enable_job ( p_job_name in varchar2 )
   is
   begin
      dbms_scheduler.enable(p_job_naam);
   end enable_job;
   --
   procedure disable_job ( p_job_name in varchar2 )
   is
   begin
      dbms_scheduler.disable(p_job_name);
   end disable_job;
   --
   procedure run_job ( p_job_naam in varchar2 )
   is
   begin
      dbms_scheduler.run_job(p_job_naam, false);   
   end run_job;  

The final item is a Dynamic Action “On page load” where you execute this javascript, displaying a message what you just did

l_message = $v("P3000_MESSAGE");
if (l_message != '') {
  apex.message.showPageSuccess( l_message );
}
$s("P3000_MESSAGE", '') ;

And that's all it needed to be done. The administrators will have the freedom to control the process, and you can work on creating more features for them.