Home » SQL & PL/SQL » SQL & PL/SQL » polling in oracle (
polling in oracle [message #418935] Wed, 19 August 2009 04:12 Go to next message
Messages: 93
Registered: October 2008
Location: Nepal
Hi all,
This is my problem.
I have to perform 100 tasks. Each task has further 3subtasks. Subtask2 can be performed after subtask1 completes and subtask3 can be performed after subtask2 completes. However those 100 can be run parallely(say 4 at a time). That ie S_TASK_1_1, S_TASK_2_1, S_TASK_3_1, S_TASK_4_1 run at the same time.
These tasks take varying amount of time. Say S_TASK_3_1 completes then S_TASK_5_1 can start. Now S_TASK_3_2 can start. If S_TASK_2_1,
S_TASK_4_1, S_TASK_5_1 finish. So S_TASK_2_2, S_TASK_4_2, S_TASK_5_2
start.Then S_TASK_1_1 finishes. So S_TASK_1_2 should wait till any subtask_2 finishes.

I tried solving it as follows.
There is table L that contains list of all main tasks.
there are other2 tables S and C.
aslong as there are tasks in L which are not in S it will take 1 task and make its entry in table S and complete task and make entry in C and continue the loop.
Now for subtask2, it will scan table L, AND C. For any task in L if it is not present in C it will make a entry in another field of S ,run subtask2 and make entry in another field of C. This will loop. this will continue till all tasks in L get performed.
I thought running 4 instances of it. So 4 different taks will be carried out. This was my idea of performing parallel tasks that are dependent.
But it didn't work out.
If all subtasks1 are completed before starting of subtask2 it works. but if any subtask1 is not complete before starting subtask2, even if subtask_any_1 completes after few seconds of starting subtask2, subtask 2 completes those that had finished earlier then remains in loop, not performing subtask_any_2.I don't know why?

My description of problem looks lousy? I will post the code if required.

Thank you
Re: polling in oracle [message #418964 is a reply to message #418935] Wed, 19 August 2009 06:48 Go to previous message
Messages: 3200
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yep. It looks lousy. Razz

The first thing that comes to my mind: do you commit after changing stuff in the "table l", so that other sessions see the changes?

It somehow looks to me, though, as if you art trying to re-invent DBMS_ALERT

Another possibility might be to use DBMS_APPLICATION_INFO instead of "table l"

Also, an approach where each task fires an "I'm finished, what next?" event might be better than having the next tasks poll all the time. That could be done with DBMS_JOB or DBMS_SCHEDULER, depending on your database version.
Previous Topic: query problem
Next Topic: Having a problem with inline query with dblink
Goto Forum:

Current Time: Mon Apr 24 04:22:49 CDT 2017

Total time taken to generate the page: 0.21204 seconds