CODEWITHSUNDEEP
sqloracle-databaseplsqlcursor
James213
James213

Reputation: 977

a better way to handle cursors as to not get maxium cursors open exceed error

I have a stored procedure that i thought was caught in an infinite loop, but after taking it apart and running the various loops individually it turns out that my code is causing a

ORA-01000: maximum open cursors exceeded

The problem seems to occur with the way i am populating the first two cursors. This may seem like a dumb questions, but i was hoping someone could give me some advice on how to better handle the population of these cursors?

Below you will find my procedure in full, and i will then break down the area that seems to be causing the problem.

Full procedure:

    procedure SplitIntrvlsAtShiftBoundaries is

vRecCount number;
RC number;
vShiftIDAtIntervalStart number;
vShiftIDAtIntervalEnd number;
vDummyRecSecs number(6,4) := 0.0;

vStartTimestamp timestamp with time zone;
vEndTimestamp timestamp with time zone;

cCurs Sys_refcursor;

vCurrentRec TMP_SHIFT_LIST_TBL%rowtype;
vCurrentInterval MACHINE_INTERVAL%rowtype;
vNewInterval MACHINE_INTERVAL%rowtype;

lProcName    CONST.PBString;


vIntervalDOW smallint; -- intervals never span a midnite boundary 
                       -- are split over midnite in prior steps.

CURSOR C1 IS SELECT * FROM TMP_SHIFT_MI_TBL ORDER BY START_DATE_TIME ASC, MACHINE_ID;
CURSOR C2 IS SELECT * FROM TMP_SHIFT_LIST_TBL ORDER BY SHIFT_START_DAY ASC, SHIFT_START_TIME; 

BEGIN

  lProcName := 'TRANSFORM_PROCESS_2.SplitIntrvlsAtShiftBoundaries';
  pb_util.logdata(3, lProcName, 'Process Started ' );

  DELETE FROM TMP_SHIFT_MI_TBL;
  DELETE FROM TMP_SHIFT_LIST_TBL;

  INSERT INTO TMP_SHIFT_MI_TBL
  SELECT *
  FROM MACHINE_INTERVAL
  WHERE  interval_state = 0
  AND start_date_time <> CONST.STARTDATE
  AND  trunc(calc_end_time) < trunc( CONST.ENDDATE) - 144;

  BEGIN
    FOR R1 IN C1
      LOOP  

        -- gets the current interval from the machine interval table by using the interval id found from the tmp_table
        SELECT * INTO vCurrentInterval 
        FROM Machine_Interval MI 
        WHERE R1.Machine_Interval_ID = MI.Machine_Interval_Id; 

        -- gets the shifts containning the start and end dates obtained from the machine interval
        Shift_pkg.getShiftsContaining(R1.start_date_time, R1.calc_end_time, cCurs, vRecCount);

        FETCH cCurs INTO vCurrentRec;
          LOOP

            -- populates the tmp_shift_list_tbl dynamically
            BEGIN          
              EXIT WHEN cCurs%notfound;

              EXECUTE IMMEDIATE 'insert into tmp_shift_list_tbl
                                 (shift_id_pk, shift_name, shift_start_day, shift_start_time, 
                                  shift_end_day, shift_end_time, site_id_fk, shift_day_id,
                                  startoffset, endoffset) 
                           VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)' 
                           USING vCurrentRec.SHIFT_ID_PK, vCurrentRec.SHIFT_NAME,
                                 vCurrentRec.SHIFT_START_DAY, vCurrentRec.SHIFT_START_TIME,
                                 vCurrentRec.SHIFT_END_DAY, vCurrentRec.SHIFT_END_TIME,
                                 vCurrentRec.SITE_ID_FK, vCurrentRec.SHIFT_DAY_ID, 
                                 vCurrentRec.STARTOFFSET, vCurrentRec.ENDOFFSET;

            END;
          END LOOP;  
          pb_util.logdata(3, lProcName, 'FOUND: ', 'found ' || vRecCount ||
                          ' shifts in machine interval: ' || R1.MACHINE_INTERVAL_ID);

        -- depending on the # of shifts found in vRecCount different cases are applied.
        CASE
        WHEN vRecCount = 1 -- apply shift id to interval   
           THEN 
             BEGIN             
               FOR R2 IN C2
                 LOOP

                    UPDATE MACHINE_INTERVAL MI
                    SET MI.SHIFT_ID = R2.SHIFT_ID_PK
                    WHERE MI.MACHINE_INTERVAL_ID = R1.MACHINE_INTERVAL_ID;

                 END LOOP;
             END;      
        WHEN vRecCount > 1 -- split up the interval between the shifts i.e. create intervals
           THEN 
            BEGIN            
              FOR R2 IN C2
                LOOP  

                  -- make copy of the current interval.
                  vNewInterval := vCurrentInterval;
                  -- set the new interval duration
                  vCurrentInterval.Interval_Duration := pb_util.intervaltosecond(R2.shift_Start_Time - 
                                                                                 vCurrentInterval.Start_Date_Time);
                  -- set the new shift id for the machine interval table
                  vCurrentInterval.Shift_ID := R2.Shift_ID_PK;

                  -- update the record.
                  DM.MachineIntervalRecordUpdate(MachineIntervalID => vCurrentInterval.Machine_Interval_ID,
                                                 StartDateTime => vCurrentInterval.Start_Date_Time,                                                 
                                                 IntervalDuration => vCurrentInterval.Interval_Duration,
                                                 IntervalCategory => vCurrentInterval.INTERVAL_CATEGORY,
                                                 NPTCategoryID => vCurrentInterval.NPT_CATEGORY_ID,
                                                 NPTControlledID => vCurrentInterval.CATEGORYTYPENUMERIC,
                                                 NPTOPStateID => vCurrentInterval.OPERATIONALSTATENUMERIC,
                                                 pExecutionSecs => vDummyRecsecs);

                  UPDATE MACHINE_INTERVAL MI
                  SET MI.Shift_ID = vCurrentInterval.Shift_ID
                  WHERE MI.Machine_Interval_ID = vCurrentInterval.Machine_Interval_ID;

                   -- set new start date time & interval duration                              
                  vNewInterval.Start_Date_Time := R2.Shift_End_Time;
                  vNewInterval.Interval_Duration := pb_util.intervaltosecond(vNewInterval.Calc_End_Time - 
                                                                             vNewInterval.Start_Date_Time);

                  -- create new record in interval table.
                  RC := DM.MachineIntervalRecordInsert(MachineIntervalRecord_IN => vNewInterval, 
                                                       pExecutionSecs => vDummyRecsecs);

                  -- set current interval to the newly created interval and loop.
                  vCurrentInterval := vNewInterval;

                END LOOP;
            END;      
        ELSE -- if no shifts are found then set id to null

          UPDATE MACHINE_INTERVAL MI
          SET SHIFT_ID = 0
          WHERE MI.MACHINE_INTERVAL_ID = R1.MACHINE_INTERVAL_ID;

        END CASE;   
      END LOOP;
      pb_util.logdata(3, lProcName, 'Process Completed ' );
      COMMIT;
  END;

exception when others then
pb_util.logdata(1, 'TRANSFORM_PROCESS_2.SplitIntrvlsAtShiftBoundaries', 'EXCEPTION THROWN (880B)', SQLERRM || ' STACK: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

END SplitIntrvlsAtShiftBoundaries;

The cursors that i'm pretty sure is causing the problem are these two:

    CURSOR C1 IS SELECT * FROM TMP_SHIFT_MI_TBL ORDER BY START_DATE_TIME ASC, MACHINE_ID;
CURSOR C2 IS SELECT * FROM TMP_SHIFT_LIST_TBL ORDER BY SHIFT_START_DAY ASC, SHIFT_START_TIME;

The area that the procedure seems to hit the maximum cursor opened exceeds:

 FOR R1 IN C1
  LOOP  

    -- gets the current interval from the machine interval table by using the interval id found from the tmp_table
    SELECT * INTO vCurrentInterval 
    FROM Machine_Interval MI 
    WHERE R1.Machine_Interval_ID = MI.Machine_Interval_Id; 

    -- gets the shifts containning the start and end dates obtained from the machine interval
    Shift_pkg.getShiftsContaining(R1.start_date_time, R1.calc_end_time, cCurs, vRecCount);

    FETCH cCurs INTO vCurrentRec;
      LOOP

        -- populates the tmp_shift_list_tbl dynamically
        BEGIN          
          EXIT WHEN cCurs%notfound;

          EXECUTE IMMEDIATE 'insert into tmp_shift_list_tbl
                             (shift_id_pk, shift_name, shift_start_day, shift_start_time, 
                              shift_end_day, shift_end_time, site_id_fk, shift_day_id,
                              startoffset, endoffset) 
                       VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)' 
                       USING vCurrentRec.SHIFT_ID_PK, vCurrentRec.SHIFT_NAME,
                             vCurrentRec.SHIFT_START_DAY, vCurrentRec.SHIFT_START_TIME,
                             vCurrentRec.SHIFT_END_DAY, vCurrentRec.SHIFT_END_TIME,
                             vCurrentRec.SITE_ID_FK, vCurrentRec.SHIFT_DAY_ID, 
                             vCurrentRec.STARTOFFSET, vCurrentRec.ENDOFFSET;

        END;
      END LOOP;  
      pb_util.logdata(3, lProcName, 'FOUND: ', 'found ' || vRecCount ||
                      ' shifts in machine interval: ' || R1.MACHINE_INTERVAL_ID);

Upvotes: 0

Views: 1198

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

Implicit cursors will never generate an ORA-01000 error because Oracle takes care of closing them when they go out of scope.

The problem in your code is with cCurs. Shift_pkg.getShiftsContaining opens the cursor and your code fetches from the cursor. But there is no code to close the cursor when you are done fetching from it. Your code will need to close the cursor when you are done fetching from it.

The code itself is a little confusing. Normally, you'd fetch from a cursor inside a loop. The code that you've posted, however, has the fetch occurring outside of the loop which means that you'll have an infinite loop if cCurs returns a row. So either cCurs never returns any data when you FETCH from it or you've got an infinite loop or the code you've posted isn't exactly what you're actually running.

Normally, you'd have something like this which will fetch from the cursor in a loop, exit the loop when no more rows are available, and then close the cursor.

LOOP
  FETCH cCurs INTO vCurrentRec;
  EXIT WHEN cCurs%notfound;

  EXECUTE IMMEDIATE 'insert into tmp_shift_list_tbl
                     (shift_id_pk, shift_name, shift_start_day, shift_start_time, 
                      shift_end_day, shift_end_time, site_id_fk, shift_day_id,
                      startoffset, endoffset) 
               VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)' 
               USING vCurrentRec.SHIFT_ID_PK, vCurrentRec.SHIFT_NAME,
                     vCurrentRec.SHIFT_START_DAY, vCurrentRec.SHIFT_START_TIME,
                     vCurrentRec.SHIFT_END_DAY, vCurrentRec.SHIFT_END_TIME,
                     vCurrentRec.SITE_ID_FK, vCurrentRec.SHIFT_DAY_ID, 
                     vCurrentRec.STARTOFFSET, vCurrentRec.ENDOFFSET;
END LOOP;

CLOSE cCurs;

That being said, there doesn't appear to be any reason to use dynamic SQL here. It would also be helpful to choose more meaningful names for your variables: C1, R1, C2, R2, and cCurs are not exactly meaningful names. And you'll generally want to break up your code into smaller blocks that are easier to read and digest.

Upvotes: 5

Related Questions