As usual, Oracle introduces new features, and we read the documentation about how they work and we start using them… but what about what is not in the documentation? That we find out by practice, experience, or accident. And that is exactly what happened to me today.
Whenever I write a procedure to be called from the scheduler (and previously via
dbms_job), if I expect the changes I make to the data to be permanent, I explictly add a
commit to the code. This makes the intention obvious to myself and others reviewing my code in the future. Today, I was reviewing someone else’s code. It was a packaged procedure that was called by the scheduler and it did not have a
I was curious to determine how the scheduler handles this. Does the scheduler issue an implicit
I scanned the online documentation and did not find any reference to whether the Oracle scheduler does implicit commits. I googled and did not find any articles that covered it. So I did what every other DBA would do — I created a test case to prove it one way or the other. Here are the steps I took.
- 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
- 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 /
- 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; /
- 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
- 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
So yes, it does an implicit commit.