TYPE SCHEDULER$_RULE_LIST IS TABLE OF sys.schedule;
/

TYPE SCHEDULER$_STEP_TYPE_LIST IS TABLE OF sys.sch;
/

TYPE SCHEDULER$_CHAIN_LINK_LIST IS TABLE OF sys.sc;
/

TYPE SCHEDULER$_STEP_TYPE IS OBJECT (
step_name VARCHAR2(32),
step_type VARCHAR2(32));
/

TYPE RE$VARIABLE_VALUE AS OBJECT (
variable_name VARCHAR2(32),
variable_data sys.anydata)

-- For the definition of RE$NV_LIST:
SELECT dbms_metadata.get_ddl('TYPE', 'RE$NV_LIST)
FROM dual;

BEGIN
  dbms_rule_adm.grant_system_privilege(
   dbms_rule_adm.create_rule_obj, 'uwclass'),
  dbms_rule_adm.grant_system_privilege(
   dbms_rule_adm.create_rule_set_obj, 'uwclass'),
  dbms_rule_adm.grant_system_privilege(
   dbms_rule_adm.create_evaluation_context_obj, 'uwclass')
END;
/

BEGIN
  dbms_resource_manager.create_consumer_group('Workers', 'Those that do
  actual work');
END;
/

BEGIN
  dbms_scheduler.create_job_class(
  job_class_name          => 'finance_jobs', 
  resource_consumer_group => 'Workers');
END;
/

BEGIN
  dbms_scheduler.create_window_group(
  window_name   => 'weeknights',
  resource_plan => 'maint_window'
  duration      -> interval '90' minute,
  window_priority => 'HIGH'
  comments => 'Off-hours maintenance window');
END;
/

-- create table
CREATE TABLE locations (
location_id NUMBER(10),
latitude    FLOAT(20),
longitude   FLOAT(20))
PCTFREE 0
PCTUSED 99;

-- create stored procedure

CREATE OR REPLACE PACKAGE sched_demo IS runno  PLS_INTEGER := 0; PROCEDURE load_data(fname VARCHAR2, yearno PLS_INTEGER); END sched_demo; / CREATE OR REPLACE PACKAGE BODY sched_demo IS PROCEDURE load_data(fname VARCHAR2, yearno PLS_INTEGER) IS  vSFile   utl_file.file_type;  vNewLine VARCHAR2(200);  p1       PLS_INTEGER;  p2       PLS_INTEGER;  locid    locations.location_id%TYPE;  latit    locations.latitude%TYPE;  longi    locations.longitude%TYPE; BEGIN   vSFile := utl_file.fopen('CTEMP', fname || TO_CHAR(yearno) ||   '.dat', 'R');   LOOP     BEGIN       utl_file.get_line(vSFile, vNewLine);       IF vNewLine IS NULL THEN         EXIT;       END IF;       p1 := INSTR(vNewLine,',',1,1);       p2 := INSTR(vNewLine,',',1,2);       locid := SUBSTR(vNewLine, 1, p1-1);       latit := SUBSTR(vNewLine, p1+1, p2-p1-1);       longi := SUBSTR(vNewLine, 15+1);       INSERT INTO locations       (location_id, latitude, longitude)       VALUES       (locid, latit, longi);     EXCEPTION       WHEN NO_DATA_FOUND THEN         EXIT;     END;   END LOOP;   COMMIT;   utl_file.fclose(vSFile);   runno := runno + 1;   utl_file.frename('CTEMP',fname || TO_CHAR(yearno) || '.dat',   'CTEMP', TO_CHAR(runno) || '.arc', TRUE); EXCEPTION   WHEN OTHERS THEN     NULL; END load_data; END sched_demo; /

-- test procedure
exec sched_demo.load_data('0101', 2007);

SELECT * FROM locations;

TRUNCATE TABLE locations;

-- rename 01012007.arc back to 01012007.dat

-- create a program with all job arguments
BEGIN
  dbms_scheduler.create_program(
  program_name   => 'Run_LOAD_DATA',
  program_type   => 'STORED_PROCEDURE',
  program_action => 'SCHED_DEMO.LOAD_DATA',
  number_of_arguments => 2,
  enabled        => FALSE,
  comments       => 'UW Test Scheduled Load');
END;
/

desc all_scheduler_programs

col owner format a10
col program_name format a20
col program_action format a40
col comments format a50

SELECT owner, program_name, program_type, program_action
FROM all_scheduler_programs;

SELECT owner, program_name, enabled, comments
FROM all_scheduler_programs;

-- set program argument
SELECT overload, position, argument_name, data_type
FROM all_arguments
WHERE object_name = 'SCHED_DEMO.LOAD_DATA';

BEGIN
  dbms_scheduler.define_program_argument(
  program_name      => 'Run_LOAD_DATA',
  argument_position => 1,
  argument_type     => 'VARCHAR2',
  default_value     => '0101');

  dbms_scheduler.define_program_argument(
  program_name      => 'Run_LOAD_DATA',
  argument_position => 2,
  argument_type     => 'NUMBER',
  default_value     => 2007);
END;
/

desc all_scheduler_job_args

col job_name format a15
col argument_type format a20
col default_value format a20

SELECT program_name, argument_name, argument_position, argument_type,
default_value
FROM all_scheduler_program_args;

-- create job
BEGIN
  dbms_scheduler.create_job(
  job_name        => 'UW_File_Load',
  program_name    => 'Run_LOAD_DATA',
  start_date      => dbms_scheduler.stime,
  repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
  end_date        => dbms_scheduler.stime+1,
  enabled         => FALSE,
  auto_drop       => FALSE,
  comments        => 'UW Demo Job');
END;
/

desc all_scheduler_jobs

col start_date format a40

SELECT job_name, program_name, start_date
FROM all_scheduler_jobs;

-- set scheduler attributes
col value format a50

SELECT attribute_name, value
FROM all_scheduler_global_attribute;

BEGIN
  dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', 2);
END;
/

SELECT attribute_name, value
FROM all_scheduler_global_attribute;

-- enable the program
exec dbms_scheduler.enable('Run_LOAD_DATA');
-- enable the job
exec dbms_scheduler.enable('UW_File_Load');

SELECT *
FROM locations;

-- test the job
exec dbms_scheduler.run_job('UW_File_Load', TRUE);

SELECT *
FROM locations;

col additional_info format a25

SELECT job_name, operation, status, additional_info
FROM all_scheduler_job_log
WHERE owner = 'UWCLASS';

SELECT job_name, state, run_count, next_run_date
FROM all_scheduler_jobs;

-- watch the job run renaming files as required to avoid a conflict

-- clean up
BEGIN
  -- stop the job
  BEGIN
    dbms_scheduler.stop_job('UW_File_Load', TRUE);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  -- drop program argument
  dbms_scheduler.drop_program_argument('Run_LOAD_DATA', 1);
  dbms_scheduler.drop_program_argument('Run_LOAD_DATA', 2);
  -- disable the program
  dbms_scheduler.disable('Run_LOAD_DATA', TRUE);
  -- drop the program
  dbms_scheduler.drop_program('Run_LOAD_DATA', TRUE);
  -- drop the job
  dbms_scheduler.drop_job('UW_File_Load', TRUE);
END;
/