APEXtras

create or replace package APEXTRAS_SCHEDULER is

  -- Author  : Roger Cohen
  -- Created : 12/05/2009 16:11:32
  -- Purpose : Example scheduler interface
  TYPE scheduler_arg_t is Record(
    argument_name VARCHAR2(100),
    argument_type VARCHAR2(100),
    default_value VARCHAR2(100),
    current_value VARCHAR2(4000)
    );
  TYPE arglist_t is TABLE OF scheduler_arg_t INDEX BY PLS_INTEGER;

  PROCEDURE ResendEmail(p_email_id   IN Number,
                        p_recipients IN VARCHAR2);
  PROCEDURE EnableJobsByCreator(p_job_creator IN USER_SCHEDULER_JOBS.job_creator%TYPE);
  PROCEDURE ScheduleResendEmail(p_email_id   IN NUMBER,
                                p_recipients IN VARCHAR2);

end APEXTRAS_SCHEDULER;
/
create or replace package body APEXTRAS_SCHEDULER is

  FUNCTION JobIsDisabled(p_job_name IN USER_SCHEDULER_JOBS.JOB_NAME%TYPE)
           RETURN BOOLEAN IS
    l_found INTEGER;
  BEGIN
    BEGIN
      SELECT 1 INTO l_found
      FROM USER_SCHEDULER_JOBS
      WHERE ((JOB_NAME = p_job_name)
      AND (UPPER(STATE) != 'ENABLED'));
    EXCEPTION
      WHEN OTHERS THEN
      l_found := 0;
    END;
    RETURN (l_found = 1);
  END;

  FUNCTION JobExists(p_job_name IN USER_SCHEDULER_JOBS.JOB_NAME%TYPE) RETURN BOOLEAN IS
    l_found INTEGER;
  BEGIN
    BEGIN
      SELECT 1 INTO l_found
      FROM USER_SCHEDULER_JOBS
      WHERE (JOB_NAME = p_job_name);
    EXCEPTION
      WHEN OTHERS THEN
      l_found := 0;
    END;
    RETURN (l_found = 1);
  END;

  PROCEDURE ScheduleSPJob(p_program_name       IN VARCHAR2,
                          p_argument_list      IN APEXTRAS_SCHEDULER.arglist_t,
                          p_schedule_date_time DATE,
                          p_force_recreate     IN BOOLEAN := FALSE,
                          p_repeat_interval    IN VARCHAR2 := '',
                          p_job_name           IN VARCHAR2 := '') IS

    l_program_name VARCHAR2(100);
    l_index        PLS_INTEGER;
    l_job_name     VARCHAR2(100);
    l_job_drop     BOOLEAN;
  BEGIN
    l_program_name := SUBSTR(REPLACE(p_program_name,'.','_'),1,30);
    --Create a unique job name, unless one was specified as a parameter
    IF (p_job_name IS NULL) THEN
      l_job_name     := DBMS_SCHEDULER.GENERATE_JOB_NAME('MYPREFIX_');
      l_job_drop     := FALSE;
    ELSE
      l_job_name     := p_job_name;
      l_job_drop     := TRUE;
    END IF;
    IF l_job_drop THEN
      BEGIN
        DBMS_SCHEDULER.drop_job(l_job_name,TRUE);
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
    END IF;
    --Drop the program if requested. This is needed for the first call
    --after the scheduled program's parameters have been changed.
    IF p_force_recreate THEN
      BEGIN
        DBMS_SCHEDULER.drop_program(program_name => l_program_name,
                                    force        => TRUE);
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
    END IF;
    BEGIN
      --A scheduler job must reference a predefined program name, so first we create one
      DBMS_SCHEDULER.CREATE_PROGRAM (program_name           => l_program_name,
                                     program_action         => p_program_name,
                                     program_type           => 'STORED_PROCEDURE',
                                     number_of_arguments    => p_argument_list.count,
                                     enabled                => FALSE,
                                     comments               => 'Automatically scheduled by ScheduleSPJob at ' || TO_CHAR(SYSDATE));

      FOR l_index in 1 .. p_argument_list.COUNT
      LOOP
        DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name            => l_program_name,
                                                argument_position       => l_index,
                                                argument_name           => p_argument_list(l_index).argument_name,
                                                argument_type           => p_argument_list(l_index).argument_type,
                                                default_value           => p_argument_list(l_index).default_value);
      END LOOP;
    EXCEPTION
      --We expect an exception on most calls to DBMS_SCHEDULER.CREATE_PROGRAM
      --because the program will already exist
      WHEN OTHERS THEN
        NULL;
    END;
    DBMS_SCHEDULER.ENABLE(l_program_name);

    --Now we have a program to reference, so create the scheduler job
    DBMS_SCHEDULER.CREATE_JOB (job_name             => l_job_name,
                               program_name         => l_program_name,
                               start_date           => p_schedule_date_time,  --NULL means schedule for immediate execution
                               repeat_interval      => p_repeat_interval,
                               comments             => 'Auto scheduled Program:' || p_program_name);

    --Set the arguments for the job
    --Could add a boolean or other type here if required
    FOR l_index in 1 .. p_argument_list.COUNT
    LOOP
      CASE UPPER(p_argument_list(l_index).argument_type)
        WHEN 'VARCHAR2' THEN
          DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
                job_name                => l_job_name,
                argument_name           => p_argument_list(l_index).argument_name,
                argument_value          => p_argument_list(l_index).current_value);
        WHEN 'NUMBER' THEN
          DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
                job_name                => l_job_name,
                argument_name           => p_argument_list(l_index).argument_name,
                argument_value          => TO_NUMBER(p_argument_list(l_index).current_value));
        WHEN 'INTEGER' THEN
          DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
                job_name                => l_job_name,
                argument_name           => p_argument_list(l_index).argument_name,
                argument_value          => TO_NUMBER(p_argument_list(l_index).current_value));
        ELSE
          DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
                job_name                => l_job_name,
                argument_name           => p_argument_list(l_index).argument_name,
                argument_value          => p_argument_list(l_index).current_value);

      END CASE;
    END LOOP;
    BEGIN
      --Enable the job
      DBMS_SCHEDULER.ENABLE (l_job_name);
    EXCEPTION
      --Sometimes the job isn't queued fast enough
      --for it to be visible to DBMS_SCHEDULER.ENABLE
      WHEN OTHERS THEN
      NULL;
    END;
    IF JobIsDisabled(l_job_name) THEN
      --If the job wasn't previously visible, try enabling it again
      DBMS_SCHEDULER.ENABLE (l_job_name);
    END IF;
  END;

  --Example procedure stub
  PROCEDURE ResendEmail(p_email_id   IN Number,
                        p_recipients IN VARCHAR2) IS
  BEGIN
    --Resend the email from email table record identified by p_email_id
    --to each of the comma-separated email addresses in string p_recipients
    NULL;
  END;

  PROCEDURE EnableJobsByCreator(p_job_creator IN USER_SCHEDULER_JOBS.job_creator%TYPE) IS
  BEGIN
    FOR I IN (SELECT JOB_NAME AS JOB_NAME
              FROM USER_SCHEDULER_JOBS
              WHERE (job_creator = p_job_creator))
    LOOP
      DBMS_SCHEDULER.ENABLE (I.JOB_NAME);
    END LOOP;
  END;

  PROCEDURE ScheduleResendEmail(p_email_id   IN NUMBER,
                                p_recipients IN VARCHAR2) IS
    l_arglist APEXTRAS_SCHEDULER.arglist_t;
  BEGIN
    l_arglist(1).argument_name := 'p_email_id';
    l_arglist(1).argument_type := 'NUMBER';
    l_arglist(1).default_value := '';
    l_arglist(1).current_value := TO_CHAR(p_email_id);

    l_arglist(2).argument_name := 'p_recipients';
    l_arglist(2).argument_type := 'VARCHAR2';
    l_arglist(2).default_value := '';
    l_arglist(2).current_value := p_recipients;

    ScheduleSPJob(p_program_name       => 'APEXTRAS_SCHEDULER.ResendEmail',
                  p_argument_list      => l_arglist,
                  p_schedule_date_time => NULL, -- Execute immediately
                  p_force_recreate     => FALSE);
  END;

end APEXTRAS_SCHEDULER;
/

Using the Oracle Scheduler in APEX