create or replace   PROCEDURE jobcontrol (
      
--===========================================================================
-- Copywright 2002, joel crainshaw and chet west
--===========================================================================
-- NAME: jobcontrol
--
-- PURPOSE: job manager for dbms_jobs  
--
-- PARAMETERS:
--            p_name = name of job stored in "jobs" table or ALL if you 
--                     want the action taken on all active jobs
--            p_action = action to take on job; options are:
--                       SUBMIT,RUN,REMOVE,STOP
--                         SUBMIT = standard submission with intervals
--                         RUN = submit and run job once ignoring interval
--                         REMOVE = remove the job (NOT IMPLEMENTED)
--                         STOP = stop this job (ie break job; NOT IMPLEMENTED)
--
-- REQUIREMENTS:
--            permissions to users for running jobs
--            create jobs table (below);
/*
CREATE TABLE JOBS ( 
NAME               VARCHAR2 (80), 
START_TIME_TYPE    VARCHAR2 (20), 
START_TIME         VARCHAR2 (4000), 
RUN_INTERVAL_TYPE  VARCHAR2 (20), 
RUN_INTERVAL       VARCHAR2 (4000), 
CODE               VARCHAR2 (4000), 
STATUS             CHAR (1)      DEFAULT 'A');

insert into jobs
(name,start_time_type,start_time,
run_interval_type,run_interval,code,status)
values
('MYJOB','SECONDS','15',
'LITERAL','SYSDATE + 30/86400',
'null;','A');

commit;
*/
--            jobs table column usage is:
--                   NAME = friendly name of job
--                   START_TIME_TYPE = SQL,LITERAL,
--                                     S,SEC,SECS,SECOND,SECONDS,  
--                                     M,MIN,MINS,MINUTE,MINUTES,
--                                     H,HR,HRS,HOUR,HOURS,
--                                     D,DAY,DAYS
--                   START_TIME = literal value (for type = LITERAL)
--                                sql stmt (for type = SQL)
--                                         (any sql stmt returning a date in 
--                                            MM/DD/YYYY HH24:MI:SS format
--                                            (ex: SELECT TO_CHAR (TRUNC (SYSDATE)
--                                                        + 1, 'MM/DD/YYYY')
--                                                         || ' ' || value
--                                                 FROM mytab
--                                                 WHERE starttime = 
--                                                       'SCHED_ALLOCATION_TIME')
--                                number (for all others)
--                   RUN_INTERVAL_TYPE = SQL,LITERAL
--                   RUN_INTERVAL = date expression in varchar2 format
--                                  (ex: NEXT_DAY(TRUNC(SYSDATE+1),'FRI')+17.5/24)
--                   CODE = job code to execute; can be simply a call to a 
--                          package.procedure which has permissions or 
--                          actual plsql code (up to 4k)
--                   STATUS = A,I (active, inactive)
-- 
--
-- MODIFICATION HISTORY
-- PERSON       DATE       COMMENTS
-- ----------   ---------- ------------------------------------------
-- joel         08/01/2002 initial development
--===========================================================================

      p_name IN VARCHAR2
     ,p_action IN VARCHAR2 DEFAULT 'SUBMIT'
   ) IS
      CURSOR c_jobs IS
         SELECT NAME
               ,start_time_type
               ,start_time
               ,run_interval_type
               ,run_interval
               ,code
           FROM JOBS j
          WHERE (   UPPER (NAME) = UPPER (p_name)
                 OR UPPER (p_name) = 'ALL'
                )
            AND status = 'A';

      v_run_date VARCHAR2 (4000);
      v_run_date_date DATE;
      v_run_interval VARCHAR2 (4000);
      v_factor NUMBER := 1;
      v_job_id BINARY_INTEGER;
   BEGIN
      FOR i IN c_jobs LOOP
         v_run_date := NULL;
         v_factor := 1;
         v_run_interval := NULL;

         -----
         ----- deal with start date/time
         -----
         IF UPPER (i.start_time_type) IN ('SQL', 'LITERAL') THEN
            IF UPPER (i.start_time_type) = 'SQL' THEN
               EXECUTE IMMEDIATE i.start_time
                  INTO v_run_date;
            ELSIF UPPER (i.start_time_type) = 'LITERAL' THEN
               v_run_date := i.start_time;
               -----
               ----- force evaluation of formula stored in table
               ----- then convert it into full date/time format
               -----
               EXECUTE IMMEDIATE 'select ' || v_run_date || ' from dual'
                  INTO v_run_date_date;
               v_run_date := TO_CHAR (
                                v_run_date_date
                               ,'MM/DD/YYYY HH24:MI:SS'
                             );
            END IF;

            v_run_date_date := TO_DATE (v_run_date, 'MM/DD/YYYY HH24:MI:SS');
         ELSE
            IF UPPER (i.start_time_type) IN
                                       ('S', 'SEC', 'SECS', 'SECOND', 'SECONDS') THEN
               v_factor := (1 / (24 * 60 * 60));
               v_run_date_date := SYSDATE + (i.start_time * v_factor);
            ELSIF UPPER (i.start_time_type) IN
                                        ('M', 'MIN', 'MINS', 'MINUTE', 'MINUTES') THEN
               v_factor := (1 / (24 * 60));
               v_run_date_date := SYSDATE + (i.start_time * v_factor);
            ELSIF UPPER (i.start_time_type) IN ('H', 'HR', 'HRS', 'HOUR', 'HOURS') THEN
               v_factor := (1 / 24);
               v_run_date_date := SYSDATE + (i.start_time * v_factor);
            ELSIF UPPER (i.start_time_type) IN ('D', 'DAY', 'DAYS') THEN
               v_factor := 1;
               v_run_date_date := SYSDATE + (i.start_time * v_factor);
            END IF;
         END IF;

         -----
         ----- deal with interval date/time
         -----
         IF UPPER (i.run_interval_type) = 'SQL' THEN
            EXECUTE IMMEDIATE i.run_interval
               INTO v_run_interval;
         ELSIF NVL (i.run_interval_type, 'LITERAL') = 'LITERAL' THEN
            v_run_interval := i.run_interval;
         END IF;

         -----
         ----- perform action
         -----
         IF UPPER (p_action) = 'SUBMIT' THEN
            DBMS_JOB.submit (
               v_job_id
              ,'begin ' || i.code || ' end;'
              ----- for testing ,'begin null; end;'
              ,v_run_date_date
              ,v_run_interval
            );
            COMMIT;
         ELSIF UPPER (p_action) = 'RUN' THEN
            DBMS_JOB.submit (
               v_job_id
              ,'begin ' || i.code || '; end;'
              ,SYSDATE
              ,NULL
            );
            COMMIT;
         ELSIF UPPER (p_action) = 'REMOVE' THEN
            NULL;
         ELSIF UPPER (p_action) = 'STOP' THEN
            NULL;
         END IF;
      END LOOP;
   END;