Hi,
Posted here as the Scheduler is/will be involved in the solution at some stage, I haven't been able to find an easy answer to my problem so before I go reading/experimenting with some more less known concepts (to me!) I felt it worth asking to check i'm heading in the right direction:
Scenario/Background
Users have facility to load excel spreadsheet data (e.g. order lines) into table via APEX screen. The loaded data is then 'validated' using PL/SQL routines reflecting business logic, the validations performed by the PL/SQL are quite complex requiring checks across other tables for existence based upon dates, similarities etc. with logging performed to provide meaningful feedback to users to aid resolution of validation failures.
As such a single validation can take 5 minutes or more depending upon the volume data in the loaded spreadsheet. These validations are executed as 'background tasks' in the application, by using the Scheduler to submit jobs:
- The job has unique name generated per submission
- The job executes the same procedure with different parameters per submission i.e the id of the loaded data to validate (e.g the order header), the generated job name
- The procedure logs progress to v$session_longops using generated job name as opname
- Multiple users can submit multiple validation jobs as background tasks
- Users can monitor progress of their background tasks
Problem
The problem with the above approach is that when the user submits a task to validate the unique job name results in multiple jobs running at the same time, this is normal behaviour but in reality what is required in this case is for second/subsequent loads to validate only if there is no other validations taking place. The reason is that the validations are data dependent e.g same part can't appear on two orders within a period of time.
Is their anyway to 'link' or 'queue' these validation jobs using 'native' scheduler functionality so that subsequent submissions execute sequentially and not in parallel? I've looked into job chains, I don't think that is quite what this is. Although I suppose the presence of a second/multiple validation jobs becomes a 'chain' only if more than one validation job happens to exit.
I am considering event based scheduling but it appears the event needed for the above would to be a custom condition. Any other suggestions appreciated.
Thanks,
Daljit
My environment details
- APEX 5.0.4.00.12
- Oracle 11g (11.2.0.1), CentOS 5
- EPG
- Internet Explorer 11 (Windows 7)
- Universal Theme