SQL & PL/SQL

Dear concerned,

I have one function in a package (databse 11g) with following code.
As listed, looping a table TASK_LIST to execute each task after confirming that it is not
already executed (by querying TASK_DONE table).

Problem I have encountered is , at times same task get executed more than once even if I am counter checking
with variable "mSUCCEED". Please guide me in this regard.


Unnikrishnan Nair.

=============
CODE LISTING:
=============
BEGIN
FOR L IN (SELECT * FROM TASK_LIST WHERE ISACTIVE = 1 )
LOOP
SELECT COUNT(*) INTO mSUCCEEDED FROM TASK_DONE WHERE TASKNAME = L.TASKNAME AND STATUS = 'DONE' ;
IF MSUCCEEDED = 0 THEN
STA := 'BEGIN '||CHR(10)
||' :mRESULT := BANK_EOD_NEW.'||L.TASKSNAME||'(:2,:3,:pErrMsg); '||CHR(10)
||'END;';
EXECUTE IMMEDIATE STA USING OUT mRESULT,IN pIDDATE,IN pHOMEBRANCH,OUT pErrMsg;
IF mRESULT = 0 THEN
pErrMsg := 'TASK '||L.TASKSNAME||' ERROR : '||pErrMsg;
ROLLBACK;
RETURN 0;
ELSE
INSERT INTO TASK_DONE (TASKNAME,STATUS,PROCESS,MKRUSR,MKRDATE,MKRBRH,MKRNODE,MKRTIME)
VALUES (mTEMP,'DONE','PREEOD',TRIM(pMKRUSR),pMKRDATE,pMKRBRH,TRIM(pMKRNODE),Systimestamp);

COMMIT;
END IF;
END IF;
END LOOP;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
pErrMsg := SQLERRM;
ROLLBACK;
RETURN 0;
END;