Create a job which runs once
create table tq84_dbms_scheduler_job_t (
tmstamp date,
text varchar2(20)
);
create or replace package tq84_dbms_scheduler_job_p as
procedure j;
end tq84_dbms_scheduler_job_p;
/
create or replace package body tq84_dbms_scheduler_job_p as
procedure j is begin
insert into tq84_dbms_scheduler_job_t values (sysdate, 'j started');
dbms_session.sleep(3);
insert into tq84_dbms_scheduler_job_t values (sysdate, 'j stopped');
commit;
end j;
end tq84_dbms_scheduler_job_p;
/
begin
dbms_scheduler.create_job (
job_name => 'TheNameOfTheJob',
job_type => 'STORED_PROCEDURE',
job_action => 'tq84_dbms_scheduler_job_p.j',
start_date => null,
repeat_interval => null, -- Run once
enabled => true
);
end;
/
select
*
from
user_scheduler_jobs
where
program_name = 'TheNameOfTheJob';
select * from tq84_dbms_scheduler_job_t order by tmstamp;
exec dbms_session.sleep(5);
select
*
from
user_scheduler_jobs
where
program_name = 'TheNameOfTheJob';
select * from tq84_dbms_scheduler_job_t order by tmstamp;
drop package tq84_dbms_scheduler_job_p;
drop table tq84_dbms_scheduler_job_t;
select
jrd.job_name,
jrd.log_date,
jrd.status,
jrd.additional_info,
jrd.actual_start_date,
jrd.errors
from
sys.dba_scheduler_job_run_details jrd
where
jrd.job_name = 'THENAMEOFTHEJOB'
order by
jrd.log_date desc
;
Create a job twice
declare
procedure go(text in varchar2) is begin -- {
dbms_output.put_line(text || ', trying to create job.');
dbms_scheduler.create_job (
job_name => 'TheNameOfTheJob',
job_type => 'PLSQL_BLOCK',
job_action => 'begin dbms_lock.sleep(5); end;',
start_date => null,
repeat_interval => null,
enabled => true
);
dbms_output.put_line(text || ', job created');
exception when others then
if sqlcode = -27477 then
dbms_output.put_line(text || ', job was started before previous ended');
end if;
end go; -- }
begin
go('first');
go('second');
dbms_lock.sleep(7);
go('third');
end;
/