I have one query in my code which executes for every 5 seconds based on one column called "JOB_PROCESS_ID".
If "JOB_PROCESS_ID" is null then it starts processing those records, after processing "JOB_PROCESS_ID" will be stored with some value. This processing will take some time to complete (say 45 seconds). This code runs continuosly checking for Null values in "JOB_PROCESS_ID".
Now I am processing some records and then another job will start executing and will take same records as my prvious jobs processing has not completed and it didnt updated "JOB_PROCESS_ID". So same records are processed more number of times, which I want to avoid.
What should be the select statement so that I get everytime unique values based on above criteria.
I would store a completion flag to indicate that I am done, not a ready to process flag.
If job_process_id is not null, then do not allow the process to start up. If the job_process_id is null allow it to proceed. The flag would be set by the current process to null and committed as the last thing that it does. and when the new process starts up, set the flag and commit as the very first thing that it does.
Last edited by beilstwh; 12-11-07 at 08:58.
You do not need a parachute to skydive. You only need a parachute to skydive twice.
It seems rather cumbersome to be "polling" for null values for job_process_id every 5 seconds -- Not to mention the overhead of executing a select every 5 seconds on your db -- Why not use a publish / subscribe type architecture such that as soon the job_process_id becomes null, you send a message (publish) the event to a subscriber who will then take action. This can all be configured using oracles dbms_alert package -- do a google search on dbms_alert for details