Oracle: DBMS_JOB

Functions and procedures

Example

variable jobno number

create table tq84_job_test (
   ts  date,
   val varchar2(100)
);

set serveroutput on
begin
   dbms_job.submit(
      :jobno,
'begin
   insert into tq84_job_test values (sysdate, (select count(*) from tq84_job_test));
   commit;
end;',
      sysdate,
      interval=> 'sysdate + 5/24/60/60' -- every 5 seconds
   );

   commit; -- <=== Important.

   dbms_output.put_line('jobno = ' || :jobno);
end;
/

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select
   broken,
   last_date,
   next_date,
   interval,
   last_sec,
   next_sec,
   what
from
   user_jobs
where
   job = :jobno
;


select * from tq84_job_test;

begin
   dbms_job.remove(:jobno);
   commit;
end;
/

See also