Oracle: DBMS_SCHEDULER

Procedures and functions

Creating a program, a schedule and a job

drop   table tq84_table purge;
create table tq84_table (
  tm   date,
  txt  varchar2(50)
);

create or replace package tq84_pkg as -- {

  procedure proc;

end tq84_pkg; -- }
/

create or replace package body tq84_pkg as -- {

  procedure proc is -- {
  begin
    insert into tq84_table values (sysdate, 'Inserted from tq84_pkg.proc');
  end proc; -- }

end tq84_pkg; -- }
/

begin -- { Creating program, schedule and job

  dbms_scheduler.create_program (
    program_name         => 'FOO_PROG', 
    program_type         => 'STORED_PROCEDURE', 
    program_action       => 'tq84_pkg.proc',
    number_of_arguments  =>  0, --  Use dbms_scheduler.define_program_argument if not 0.
    enabled              =>  true,
    comments             => 'Scheduling example'
  );
  -- select enabled from user_scheduler_programs where program_name = 'FOO_PROG';

  dbms_scheduler.create_schedule (
    schedule_name   => 'FOO_SCHEDULE',
    start_date      =>  systimestamp,
    repeat_interval => 'freq=minutely; bysecond=30',
    end_date        =>  null,
    comments        => 'Minute Schedule'
  );
  -- select start_date, repeat_interval, end_date from user_scheduler_schedules where schedule_name = 'FOO_SCHEDULE';

  dbms_scheduler.create_job (
    job_name      => 'FOO_JOB',
    program_name  => 'FOO_PROG',
    schedule_name => 'FOO_SCHEDULE',
    enabled       =>  true,
    comments      => 'Job, combining FOO_SCHEDULE with FOO_PROG'
);
  -- select enabled from user_scheduler_jobs where job_name = 'FOO_JOB';

  -- select log_id, log_date, status from user_scheduler_job_log where job_name = 'FOO_JOB';

end; -- }
/

begin -- { Cleaning up

  begin
    dbms_scheduler.drop_job      ('FOO_JOB'     );
  exception when others then
     if sqlcode != -27475 then
  --
  -- Exception -27475 is raised if job does not exist.
  -- Do nothing if job does not exists, else
  -- raise error
  --
        raise;
     end if;
  end;

  begin
    dbms_scheduler.drop_schedule ('FOO_SCHEDULE');
  exception when others then
     if sqlcode != -27476 then
     -- Similar logic as above
        raise;
     end if;
  end;

  begin
    dbms_scheduler.drop_program  ('FOO_PROGRAM' );
  exception when others then
     if sqlcode != -27476 then
     -- Similar logic as above
        raise;
     end if;
  end;

end; -- }
/

drop table    tq84_table;
drop package  tq84_pkg;

Object/table types

Logging levels

See also

Links