Oracle Database Discussions

Please remove this part from your code, since it is a bug:

EXCEPTION
WHEN OTHERS THEN
pErrMsg := SQLERRM;
ROLLBACK;
RETURN 0;

"do not be a sheep in wolf's clothing." ;)
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5080528400346352393#tom5211388400346093295

And do not commit in a loop.
Commit only once, when your complete transaction is done.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4951966319022

Edited by: Hoek on Aug 13, 2012 11:47 AM

And where do you assign a value to mTEMP?

Can you not find all the tasks which are not done in one sql statement?
Do you have a common column between task_done and task_list, eg task_id.
If not, you should have.

Assuming you have a column task_id in task_list and task_done:

for cur in
(
SELECT tl.* FROM TASK_LIST  tl
   WHERE ISACTIVE = 1 
   and not exists(select 1 from task_done td where td.task_id = tl.task_id)
)
loop
  -- only deal with tasks  from the select here
  null; 
end loop;
 

Thanks for reply, but my question is regarding the possibilities of getting 1 task executed twice.
the listed code is not the entire code, but the structure of function only.

yes there is a common column.
but my problem is ,some of the tasks in the loop get executed more than once.
note :this happens mostly when the execution time is too long

thanks for the reply.

Entire code is not posted.

Just the flow of code is given. please assume that all those values are assigned and working fine.
Suppose 5 rows are to be processed in the loop. some task say TASK3 get executed twice even though its occurrence
in the list for loop is only 1.
This happens when execution takes a long duration.

thanks .

I think this happens because of Duplicate Data in TASK_LIST table. Did you check that? If there are Duplicates, either remove them or use a Distinct keyword. However, you would not get Distinct Data with ' SELECT *', you will have to fetch particular columns to get a Distinct. Moreover, you are just using the TASKNAME in the Loop, so why do a "Select *"? Its a Bad practice.
Using this, you will not have to query the Task_Done table, to ensure whether the Job is processed.

Plus, the execution time of the code would not be related to Duplicate Tasks being processed. AFAIK, When the "SELECT * FROM TASK_LIST WHERE ISACTIVE = 1" cursor is processed, any change in the data would not be reflected in the current execution. (I stand corrected, if I am wrong.)

Edited by: Purvesh K on Aug 30, 2012 6:07 PM

>
And do not commit in a loop.
Commit only once, when your complete transaction is done.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4951966319022
>
Well - make up your mind. Should OP commit only once? Or when complete transaction is done? ;)

This code appears to implement a batch process where each step is independent of the other steps. As such each loop iteration does, in fact, represent a different transaction.

Each iteration of the loop is execting a (potentially different) function. Each function could very well be a 'complete transaction' that uses entirely different schemas, objects, tables, queries, etc.

If so then a commit after every function call IS a commit when the transaction is done. If this procedure is nothing more that a batch job that executes a series of independent steps then it is appropriate to issue a COMMIT after each step.

The AskTom link you cited applies to the traditional 'commit within a transaction' case. That, and similar cases, represent users doing frequent commits because they think they are saving resources or protecting the work already done even though there is still more work to do on those same objects.

omg. this code is so full of bugs, I don't even know how to comment this garbage.
I assume it is not your code and you just have to maintain it. Poor guy. Is the other one already fired? I do hope so.

Well lets start with the most obvious problems.

1) Remove all COMMIT and ROLLBACK commands from it.
2) Remove the when others exception handler from it.

Tell us the result when running it again.

>
This happens when execution takes a long duration.
>
As Purvesh already state a single execution of this one procedure cannot execute the same task twice unless that task is present twice in the initial SELECT that defines the cursor.

So another possibility is that the procedure is being called a second time while it is still executing the first time.

This can happen when a DBMS_JOB is scheduled to execute and calls the function or if the function is called a second time by another process or user.

There is nothing in your code that locks the task records being processed so a second function call might very well try to execute the same tasks.
Your cursor is

FOR L IN (SELECT * FROM TASK_LIST WHERE ISACTIVE = 1 )

But it doesn't have a FOR UPDATE clause to lock those records. So another query for tasks "WHERE ISACTIVE = 1" will get the same records.

In fact, I don't see anything in the code you posted that sets ISACTIVE to a value other than '1' but you likely have that somewhere in the procedure.