This discussion is archived
6 Replies Latest reply: Sep 11, 2013 11:33 AM by JonMatunas RSS

ORA-01002 fetch out of sequence - not committing any data

JonMatunas Newbie
Currently Being Moderated

We have a vendor package that returns a ref cursor, we want to go through the cursor results and do additional processing and then return the updated cursor results.

 

The problem I'm running into when returning the cursor is the "ORA-01002 fetch out of sequence" error.

 

Is calling a procedure to get cursor results, going through cursor results, then return the cursor even possible? My other thought was to dump the cursor results into a Global Temp table, but didn't know if there was an easier way with constructing a temp table.

 

Here is the code:

 

create or replace

PACKAGE BODY NU_MANPOWER AS

 

  PROCEDURE GETAVAILABLETIMEWINDOWS

  (acOrderType    IN VARCHAR2,

  acCompany       IN VARCHAR2,

  acOffice        IN VARCHAR2,

  acPromote       IN VARCHAR2,

  adateStart      IN TIMESTAMP,

  adateEnd        IN TIMESTAMP,

  anResultCode    OUT NUMERIC,

  curResults      OUT SYS_REFCURSOR) AS

 

  -- Variables

  curAvailTimeWindows   sys_refcursor;

  v_START_DATE          DATE;

  v_FINISH_DATE         DATE;

  v_DAYS_BETWEEN        NUMERIC := 0;

  v_SEARCHDATE_START    VARCHAR2(16);

  v_SEARCHDATE_END      VARCHAR2(16);

  v_START_CHAR          VARCHAR2(16);

  v_END_CHAR            VARCHAR2(16);

   

  BEGIN

 

  FAST.MANPOWER.GETAVAILABLETIMEWINDOWS(acOrderType, acCompany, acOffice,

  acPromote, adateStart, adateEnd, anResultCode, curAvailTimeWindows);

 

  IF anResultCode = 0 THEN

   v_START_DATE := adateStart;

   v_FINISH_DATE := adateEnd;

  

   v_DAYS_BETWEEN := v_FINISH_DATE - v_START_DATE;  

 

   LOOP

    FETCH curAvailTimeWindows INTO recAvailableTime;

      EXIT WHEN curAvailTimeWindows%NOTFOUND;

  

    FOR i in 1..v_DAYS_BETWEEN

      LOOP         

      v_SEARCHDATE_START :=  to_char(v_START_DATE,'YYYY-MM-DD') || ' 00:01';

      v_SEARCHDATE_END :=  to_char(v_START_DATE,'YYYY-MM-DD') || ' 23:59';

           

      IF to_char(recAvailableTime.time_window_start,'YYYY-MM-DD HH24:MI') = v_SEARCHDATE_START

      AND to_char(recAvailableTime.time_window_stop,'YYYY-MM-DD HH24:MI') = v_SEARCHDATE_END THEN      

        t_dates(i) := v_START_DATE;

        t_available_time(i) := recAvailableTime.available_minutes;

       

        SELECT SUM(order_time)

        INTO t_scheduled_time(i)

        FROM fast.orders

        WHERE ORDER_SCHED_DATE >= to_date(to_char(v_START_DATE,'YYYY-MM-DD') || ' 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

        AND ORDER_SCHED_DATE <= to_date(to_char(v_START_DATE,'YYYY-MM-DD') || ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

        AND district = acCompany || '-' || acOffice;

        

        IF t_available_time(i) < t_scheduled_time(i) THEN

          t_time_full(i) := 'T';

        ELSE

          t_time_full(i) := null;       

        END IF;

      END IF;

          

      v_START_DATE := v_START_DATE + 1;

     

      END LOOP;

     

      v_START_DATE := adateStart;

           

   END LOOP;

    

  ELSE

    NULL;

  END IF;

 

  curResults := curAvailTimeWindows;

 

  EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK);   

    anResultCode := 1;

 

  END GETAVAILABLETIMEWINDOWS;

 

END NU_MANPOWER;

 

Oracle version: 10.2.0.3.0

 

It is not getting caught by the EXCEPTION block - it appears when trying to invoke the procedure and view the cursor results.

Thanks for your help.

 

Jon

  • 1. Re: ORA-01002 fetch out of sequence - not committing any data
    Mustafa KALAYCI Journeyer
    Currently Being Moderated

    please provide FAST.MANPOWER.GETAVAILABLETIMEWINDOWS plsql code too. there might be a opening problem with cursor.

     

    also please search before open a thread! there is a similar thread here: ORA-01002: fetch out of sequence

  • 2. Re: ORA-01002 fetch out of sequence - not committing any data
    JonMatunas Newbie
    Currently Being Moderated

    Thanks - I looked at that one already that is calling the package / procedure from C#.  I can't even get the procedure to run in SQL Developer yet.


    FAST.MANPOWER.GETAVAILABLETIMEWINDOWS is vendor code - it has worked for the past several years.  I can call the procedure and loop through the results.  If I removed the " LOOP    FETCH curAvailTimeWindows INTO recAvailableTime;" part and just return the cursor results (call to FAST.MANPOWER.GETAVAILABLETIMEWINDOWS), it works and returns the data as expected, so I am confident that the procedure is working correctly.  Our goal here is to not make any changes to the vendor code (due to a number of reasons).

     

    Here is the code:

     

    PACKAGE BODY        "MANPOWER"

    IS

     

     

    -----------------------------------------------------------------------------

    /*

        Constants...

    */

    -----------------------------------------------------------------------------

     

     

    STRING_QUOTE   CONSTANT VARCHAR2(1) := '''';

     

     

    -----------------------------------------------------------------------------

    /*

        GetAvailableTimeWindows

    */

    -----------------------------------------------------------------------------

    PROCEDURE GetAvailableTimeWindows (acOrderType     IN FAST.ORDERS.ORDER_TYPE%TYPE,

                                       acCompany       IN FAST.OFFICE.COMPANY%TYPE,

                                       acOffice        IN FAST.OFFICE.OFFICE%TYPE,

                                       acPromote       IN VARCHAR2,

                                       adateStart      IN DATE,

                                       adateEnd        IN DATE,

                                       anResultCode    OUT NUMERIC,

                                       curResults      OUT curResultSet)

    IS

        l_district      ManPowerCalc.DistrictKey;

        l_avail_times   ManPowerCalc.AvailableTimeTable;

        l_avail_time    ManPowerCalc.AvailableTimeRow;

        l_index_avail   PLS_INTEGER;

        l_period_avail  ManPowerCalc.TimePeriod;

        l_period_range  ManPowerCalc.TimePeriod;

        l_promote       BOOLEAN;

        l_count         PLS_INTEGER;

    BEGIN

        -- Initialize error code to none

        anResultCode := ERROR_NONE;

     

     

        -- Clear the table

        DELETE FROM GT_MANPOWER_RESULTS;

     

     

        -- Validate the parameters

        IF (TableCount('FAST.ORDER_TYPES', 'order_type = ' || STRING_QUOTE || acOrderType || STRING_QUOTE) = 0) THEN

            anResultCode := ERROR_INVALID_ORDER_TYPE;

        ELSIF (TableCount('FAST.OFFICE', 'company = ' || STRING_QUOTE || acCompany || STRING_QUOTE) = 0) THEN

            anResultCode := ERROR_INVALID_COMPANY;

        ELSIF (TableCount('FAST.OFFICE', 'office = ' || STRING_QUOTE || acOffice || STRING_QUOTE) = 0) THEN

            anResultCode := ERROR_INVALID_OFFICE;

        ELSIF (adateStart IS NULL) THEN

            anResultCode := ERROR_INVALID_DATE_START;

        ELSIF (adateEnd IS NULL) THEN

            anResultCode := ERROR_INVALID_DATE_END;

        END IF;

     

     

        -- No errors so far...

        IF (anResultCode = ERROR_NONE) THEN

     

     

            -- Set up parameters and call on ManPowerCalc procedure to get available time summary

            --   records...

            l_district := acCompany || DBMBase.DISTRICT_SEPARATOR || acOffice;

            IF (acPromote = DBMBase.BOOL_STRING_YES) THEN

               l_promote := TRUE;

            ELSIF (acPromote = DBMBase.BOOL_STRING_NO) THEN

               l_promote := FALSE;

            END IF;

            ManPowerCalc.GetAvailableTimes(l_avail_times, adateStart, adateEnd,

                l_district, acOrderType, TRUE, l_promote);

            --dbms_output.put_line('Available time count = ' || l_avail_times.COUNT);

     

     

             l_period_range.m_start := adateStart;

             l_period_range.m_stop := adateEnd;

     

     

            -- Add the results to the temporary table: sum the minute counts for

            --   duplicate time window start/stop pairs (which wil correspond to different

            --   skill levels if using promotion).

            l_index_avail := l_avail_times.FIRST;

            LOOP

                EXIT WHEN l_index_avail IS NULL;

     

     

                l_avail_time := l_avail_times(l_index_avail);

                l_period_avail := ManPowerCalc.GetAvailableTimePeriod(l_avail_time);

     

     

                -- The available time period must be contained in the given start/end range.

                IF (ManPowerCalc.IsPeriodContained(l_period_range, l_period_avail)) THEN

                   SELECT COUNT(*)

                       INTO l_count

                       FROM GT_MANPOWER_RESULTS

                       WHERE time_window_start = l_period_avail.m_start

                         AND time_window_end = l_period_avail.m_stop;

                   IF (l_count > 0) THEN

                       UPDATE GT_MANPOWER_RESULTS

                           SET available_minutes = available_minutes + l_avail_time.available_minutes,

                               assigned_non_scheduled_mins = assigned_non_scheduled_mins + l_avail_time.unscheduled_minutes

                         WHERE time_window_start = l_period_avail.m_start

                           AND time_window_end = l_period_avail.m_stop;

                   ELSE

                       INSERT INTO GT_MANPOWER_RESULTS

                           (

                           time_window_start,

                           time_window_end,

                           available_minutes,

                           assigned_non_scheduled_mins

                           )

                       VALUES

                           (

                           l_period_avail.m_start,

                           l_period_avail.m_stop,

                           l_avail_time.available_minutes,

                           l_avail_time.unscheduled_minutes

                           );

                   END IF;

                END IF;

     

     

                l_index_avail := l_avail_times.NEXT(l_index_avail);

            END LOOP;

     

     

        END IF; -- No parameter error

     

     

        -- Open the reference cursor

        OPEN curResults FOR SELECT time_window_start,           -- date and time

                                   time_window_end,             -- date and time

                                   available_minutes,           -- number of minutes available in time window

                                   assigned_non_scheduled_mins  -- number of unscheduled minutes assigned to time window

            FROM fast.gt_manpower_results

            ORDER BY (time_window_end - time_window_start), time_window_start;

     

     

    EXCEPTION

        -- All exceptions, set result code to general error and open the (empty) cursor anyway.

        WHEN OTHERS THEN

            anResultCode := ERROR_GENERAL;

            OPEN curResults FOR SELECT time_window_start,

                                       time_window_end,

                                       available_minutes,

                                       assigned_non_scheduled_mins

                FROM fast.gt_manpower_results;

     

     

    END;

     

     

    --------------------------

    END; -- FAST.ManPower body

    --------------------------

     

     

    Thanks for your help.

  • 4. Re: ORA-01002 fetch out of sequence - not committing any data
    Hoek Guru
    Currently Being Moderated

    Besides previous link user[somenumberi'llneverremember] posted, remove the bugs you call 'exception handlers'.

    Read, reread, until you understand why here:

    Ask Tom &amp;quot;Is there an easy way to make a ctas&amp;quot;

  • 5. Re: ORA-01002 fetch out of sequence - not committing any data
    Mustafa KALAYCI Journeyer
    Currently Being Moderated

    I am not familiar this error, didn't see it before but what would I do if I had it:

     

    in your verndors code, it seems fine except

    IF (anResultCode = ERROR_NONE) THEN

    if statement has no ELSE, there might be an bug at that point. beyond that if it works fine then I would check in my code after calling FAST.MANPOWER.GETAVAILABLETIMEWINDOWS whether cursor is open or not using "IF curAvailTimeWindows%isopen then "

    again if cursor is opened well, then I debug the code to find out which exact point you got this error (which line cause your code go to exception block).

  • 6. Re: ORA-01002 fetch out of sequence - not committing any data
    JonMatunas Newbie
    Currently Being Moderated

    Thanks everyone for your feedback.  As one of the articles pointed out - PL/SQL cursor loop implicity are fetching and it is causing the issue.  So I'll go the path of the global temp table.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points