oracle AQ for scheduled jobs

Question

We have a process that currently uses the Oracle DBMS_SCHEDULER to execute. The process runs every time the backend receives data from an external device. The Oracle scheduler is set up such that a job is created on reception of external data, the job executes immediately, and the job is destroyed automatically when complete.

The issue here is that multiple devices could be sending up data at the same time. Because the process involves obtaining exclusive locks at different points throughout execution, occasionally one job will step on another that is already executing (it gets rejected with ORA-20000).

I would like to somehow use the Oracle AQ (advanced queueing) mechansim in order to solve this issue. I envision a queue of jobs that are executed FIFO. Even if external device B sends up data while the process is currently processing data from external device A, the queue prevents processing of data from device B until processing for device A is complete.

There doesn't seem to be a specific section on this type of AQ processing in the Oracle documentation, but it seems like it would be a pretty common scenario. Has anyone else solved this type of issue with Oracle AQ?

Answer 1

You can implement it like this: The scheduler job program is implemented as infinite - never failing loop. This loop withdraws messages from AQ and processes them. The scheduler job is submitsted only once - from event system even trigger AFTER STARTUP ON DATABASE.

It does not use any scheduler chains nor any other advanced scheduler options. Simply the scheduler job is fired by the trigger when the database stars, the job "never finishes".

But I think dbms_scheduler is very mighty nowadays and there are ways how to limit parallel executions for a schedule.

PS: ORA-20000 is user defined error code. The database never returns such an error by its own.