CODEWITHSUNDEEP
oracle-databaseperformanceplsqlcursorbulk-collect
Amy2015
Amy2015

Reputation: 35

Issue with Bulk Collect

I have a procedure where I need to fetch data from a cursor using bulk collect. The issue with this approach is - sometimes the records are getting processed and sometimes they arent. I am unable to identify the root cause. When I try to debug the issue, toad is unresponsive and times out. Here is the code. Please help!

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;

Upvotes: 0

Views: 621

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

It looks like you've commented out the exit as part of your attempt at debugging - and that is what is causing Toad to be unresponsive, as it means the loop never exits (as Tony Andrews pointed out).

With the exit where you had it, you won't process anything if the cursor query finds fewer than 100 rows, as you'll see PRCURSOR%NOTFOUND even though some data was actually retrieved. And if it finds more than 100 rows you'll process the first batch, but then will still lose records from the last batch of less than 100.

So you need to move your exit to the end of the loop and change the condition:

  LOOP
  FETCH PRCURSOR BULK COLLECT INTO vEventRec, vEventPartitionDate LIMIT 100;
  --SVOLTP.PKGSVOLTPLOCK.SLEEP(1);
  FORALL i IN 1 .. vEventRec.COUNT
     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);
  EXIT WHEN vEventRec.COUNT < 100;
  END LOOP;

I've also changed the FORALL loop to use 1..count rather than first..last so it won't have a problem if the last actual batch is exactly 100 rows, and the next one gets zero rows - it'll do nothing in the FORALL and then exit.

This scenario is covered in the second half of this Oracle Magazine article ('Kicking the %NOTFOUND Habit').

Upvotes: 1

Related Questions