I have an Oracle 11.2g on which I use Scheduled Jobs to get some regular work done. There are around 15 jobs running 24/7.
The time interval at which those jobs run varies between 1 second for some of the jobs, and 5 minutes for some other jobs. Each job starts a single stored procedure (different for each scheduled job). The job is created by
declare begin DBMS_SCHEDULER.CREATE_JOB (job_name => 'TRANSPORT_ORDER_POLLING_JOB', job_type => 'PLSQL_BLOCK', job_action => 'begin PROC_POLL_TRANSPORT_ORDERS; end;', start_date => SYSDATE, repeat_interval => 'FREQ=SECONDLY;INTERVAL=1', auto_drop => FALSE, -- Default ist TRUE enabled => TRUE); -- Default ist FALSE commit; end;
The corresponding procedure would be defined as
create or replace procedure PROC_POLL_TRANSPORT_ORDERS is pTaskID varchar2(30); pErrorCode number; begin pTaskID := 'PROC_POLL_TRANSPORT_ORDERS'; -- ...do the real work here... exception when others then LOGGER.INSERT_LOG_MESSAGE (pTaskID, 'PROC_POLL_TRANSPORT_ORDERS failed; ()', LOGGER.LOG_LEVEL_ERROR); LOGGER.INSERT_ERROR (pTaskID); rollback; end PROC_POLL_TRANSPORT_ORDERS;
The aim of the wrapper procedure is to catch all exceptions and log them. If one of the jobs takes longer than its execution time interval, the job seems to be executed at the next time interval possible. Although not generally desirable, this sitaution may occur from time to time, since each job just polls its own table in search for new work to do, while the work load of a single table quite depends on physical conditions of my system.
My problem is, that sometimes the scheduler leaves a job after execution in the status RUNNING, although it has been ended hours ago. This in turn prevents the scheduler from starting the job anew, leaving me in the situation where my routing might stop working correctly.
My Question about this situation is: am I using the correct technology for my aim? What am I doing wrong?
As a note: I can not use triggers for this because I need to use library functions which would also alter some attributes of the row which triggered the call itself. This would raise an exception by oracle, hence the scheduled jobs.