2

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.

2
  • Anything in DBA_SCHEDULER_JOB_RUN_DETAILS that may indicate what has been happening? It could be a bug - I've certainly seen problems with broken jobs for no apparent reason in earlier 11.2 releases
    – Philᵀᴹ
    Commented May 14, 2014 at 14:26
  • @Phil: thanks for the hint, I will investigate this.
    – mruether
    Commented May 14, 2014 at 15:49

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.