DBMS_SCHEDULER and Implicit Commits

1. Create the test table.

SQL> create table babette ( thedate date, timesupdate number);
 SQL> insert into babette values ( sysdate, 0, null, null);
 SQL> commit;
 SQL> select to_char(thedate, 'YYYY/MM/DD HH24:MI:SS'), timesupdate from babette;
 
 TO_CHAR(THEDATE,'YY TIMESUPDATE
 ------------------- -----------
 2007/02/26 14:50:21 0

1. Create the procedure.

SQL> create or replace procedure update_babette is
  2 begin
  3 update babette set thedate = sysdate, timesupdate = timesupdate + 1;
  4 end;
  5 /

1. Create the Scheduler job.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
  job_name => 'BABETTE_JOB',
  job_type => 'STORED_PROCEDURE',
  job_action => 'avail.update_babette',
  start_date => sysdate,
  repeat_interval => 'FREQ=MINUTELY',
  enabled => TRUE);
 END;
 /

1. Confirm that the job is running.

select log_id, to_char(log_date,'YYYY/MM/DD HH24:MI') log_date,
  owner, job_name, status,
  to_char(req_start_date, 'YYYY/MM/DD HH24:MI') start_date,
  to_char(actual_start_date,'YYYY/MM/DD HH24:MI') actual_start_dt
 from DBA_SCHEDULER_JOB_RUN_DETAILS
 where job_name = 'BABETTE_JOB' and actual_start_date > sysdate - 5 
 order by owner, actual_start_dt;
 
 LOG_ID LOG_DATE OWNER JOB_NAME STATUS ACTUAL_START_DT 
 432292 2007/02/26 15:10 AVAIL BABETTE_JOB SUCCEEDED 2007/02/26 15:10 
 432294 2007/02/26 15:11 AVAIL BABETTE_JOB SUCCEEDED 2007/02/26 15:11

1. Check if the table updates are kept and visible to other sessions.

SQL> select to_char(thedate, 'YYYY/MM/DD HH24:MI:SS'), timesupdate from babette;
 
 TO_CHAR(THEDATE,'YY TIMESUPDATE
 ------------------- -----------
 2007/02/26 15:10:23 3