У меня есть процедура, в которой мне нужно получить данные из курсора, используя массовый сбор. Проблема с этим подходом заключается в том, что иногда записи обрабатываются, а иногда нет. Я не могу определить первопричину. Когда я пытаюсь отладить проблему, жаба не отвечает и истекает время ожидания. Вот код. Пожалуйста помоги!
PROCEDURE GetPendingItems(pprogramidlst IN VARCHAR2, EventCur OUT cur)
AS
vSessionId NUMBER;
vDefaultValue svoltp.param.DefaultValue%TYPE;
TYPE EventRec IS TABLE OF TrigEventQueue.TrigEventQueueId%TYPE;
TYPE EventPartitionDate IS TABLE OF TrigEventQueue.PartitionDate%TYPE;
vEventRec EventRec;
vEventPartitionDate EventPartitionDate;
vOldestPossibleDate DATE;
vtrigeventqueueid VARCHAR2 (250);
vprogramidlst VARCHAR2 (250);
vETInterval number;
VCOUNT NUMBER;
VCOUNT1 NUMBER;
CURSOR PRCURSOR(vCount1 NUMBER) IS
SELECT TrigEventQueueId, PartitionDate FROM TRIGEVENTQUEUE A
WHERE TrigEventQueueId IN (SELECT TrigEventQueueId
FROM ( SELECT teq.TrigEventQueueId, teq.PartitionDate, teq.EventProcessingSessionId,teq.TrigEventStateId
FROM svoltp.TrigEventQueue teq,
programtrigevent pte,
trigevent te
WHERE teq.TrigEventStateId = gcEventStatePending
AND teq.EventProcessingSessionId IS NULL
AND teq.ProgramTrigEventId = pte.ProgramTrigEventId
AND pte.TrigEventId = te.TrigEventId
AND teq.PartitionDate >
(SYSDATE - (te.NumHoursUntilEventExpired / 24))
AND teq.PartitionDate > vOldestPossibleDate
ORDER BY teq.TrigEventCreatedTS) a
WHERE ROWNUM <= vCount1)
FOR UPDATE OF A.TrigEventQueueId, A.PARTITIONDATE SKIP LOCKED;
BEGIN
vSessionId := TrigEventSessionIdSeq.NEXTVAL;
vprogramidlst := pprogramidlst;
SELECT DefaultValue
INTO vDefaultValue
FROM svoltp.Param
WHERE ParamId = gcMaxPenEventsParam;
SELECT DefaultValue
INTO vETInterval
FROM svoltp.Param
WHERE ParamId = 2755;
-- Use MAX number of expiry hours to identify an oldest possible date/time that any event could be picked up for.
SELECT SYSDATE - (MAX (NumHoursUntilEventExpired) / 24)
INTO vOldestPossibleDate
FROM trigevent;
SELECT COUNT(1) INTO VCOUNT1
FROM ( SELECT teq.TrigEventQueueId, teq.PartitionDate
FROM svoltp.TrigEventQueue teq,
programtrigevent pte,
trigevent te
WHERE teq.TrigEventStateId = gcEventStatePending
AND teq.EventProcessingSessionId IS NULL
AND teq.ProgramTrigEventId = pte.ProgramTrigEventId
AND pte.TrigEventId = te.TrigEventId
AND teq.PartitionDate >
(SYSDATE - (te.NumHoursUntilEventExpired / 24))
AND teq.PartitionDate > vOldestPossibleDate
ORDER BY teq.TrigEventCreatedTS) a
WHERE ROWNUM <= vDefaultValue;
IF VCOUNT1 > 0 THEN
SELECT count(1) into vcount FROM ETINSTANCESTATUS
WHERE datediff ('SS', INSTANCEUPDATETIME, SYSDATE) < vETInterval;
if vcount > 0 then
vcount1 := round(vcount1/vcount);
else
vcount1 := vcount1;
end if;
END IF;
OPEN PRCURSOR(vcount1);
LOOP
FETCH PRCURSOR BULK COLLECT INTO vEventRec, vEventPartitionDate LIMIT 100;
-- EXIT WHEN PRCURSOR%NOTFOUND;
--SVOLTP.PKGSVOLTPLOCK.SLEEP(1);
FORALL i IN vEventRec.FIRST .. vEventRec.LAST
UPDATE svoltp.TrigEventQueue teq
SET teq.EventProcessingSessionId = vSessionId,
teq.TrigEventStateId = gcEventStateLocked, --6 : Locked State
teq.LastUser = 1003,
teq.LastUpdate = SYSDATE
WHERE teq.TrigEventQueueId = vEventRec (i)
AND teq.PartitionDate = vEventPartitionDate (i);
END LOOP;
COMMIT;
CLOSE PRCURSOR;
OPEN EventCur FOR
SELECT TrigEventQueueId, ProgramTrigEventId, PartitionDate
FROM svoltp.TrigEventQueue teq
WHERE teq.EventProcessingSessionId = vSessionId
AND teq.TrigEventStateId = gcEventStateLocked
AND teq.PartitionDate > vOldestPossibleDate;
EXCEPTION
WHEN OTHERS
THEN
OPEN EventCur FOR
SELECT 1
FROM DUAL
WHERE 1 = 2;
END GetPendingItems;
when others
не поможет вам найти какие-либо реальные проблемы, если проблема не в этом...) - person Alex Poole   schedule 22.07.2015