Oracle: DBMS_SCHEDULER.CREATE_PROGRAM

Example

create table tq84_dbms_scheduler_tab (
   tmstamp    date,
   text       varchar2(20)
);

create or replace package tq84_dbms_scheduler
   authid definer
as
   procedure p;
end tq84_dbms_scheduler;
/

create or replace package body tq84_dbms_scheduler as

   procedure p is begin
      insert into tq84_dbms_scheduler_tab values (sysdate, 'p started');
      dbms_session.sleep(15);
      insert into tq84_dbms_scheduler_tab values (sysdate, 'p stopped');

      commit;
   end p;

end tq84_dbms_scheduler;
/


begin

   dbms_scheduler.create_program (
      program_name         => 'p',
      program_type         => 'STORED_PROCEDURE',
      program_action       => 'tq84_dbms_scheduler.p',
      number_of_arguments  =>  0, --  Use dbms_scheduler.define_program_argument if not 0.
      enabled              =>  true,
      comments             => 'A test, just a test'
   );

end;
/

select program_name, enabled from user_scheduler_programs;

begin
--
-- The program is run by or via a job:
--
-- Note create_job is overloaded.
-- This overload doesn't have the
-- job_type parameter:

   dbms_scheduler.create_job (
      job_name         => 'p_job',
      program_name     => 'p',
      start_date       =>  null,
      repeat_interval  =>  null,
      enabled          =>  true
   );

end;
/

select
   program_name
   enabled,
   state,
   schedule_type
from
   user_scheduler_jobs
where
   job_name = 'P_JOB';
   

begin
   dbms_session.sleep(16);
end;
/

   
select count(*) from user_scheduler_jobs where job_name = 'P_JOB';

select * from tq84_dbms_scheduler_tab order by tmstamp;

begin
   dbms_scheduler.drop_program('p');
end;
/
drop package tq84_dbms_scheduler;
drop table   tq84_dbms_scheduler_tab;

See also