ORA-01001 Invalid Cursor error

From: Glenn Griffith <gagriff_at_bellsouth.net>
Date: Thu, 23 Aug 2001 11:55:57 -0400
Message-ID: <yH9h7.17935$V7.530590_at_e3500-atl2.usenetserver.com>


Hello,
  I'm hoping someone can help me with a PL/SQL problem. I'm trying to write a program which will merge data retrieved from 2 cursors and insert the records into a new table. The code is as follows:

DECLARE    CURSOR C_PV IS

      SELECT PS006.CT058_MACHINE_ID, PS006.CT058_CT006_ID, PS006.DURATION,
             PS006.PS001_PV_NUM, PS006.START_TIME_ACT
        FROM PS006, PS001
       WHERE PS006.CT058_MACHINE_ID = '45'
         AND PS006.CT058_CT006_ID = 'BUR'
         AND PS006.START_TIME_ACT >= '15-AUG-01'
         AND PS001.PV_NUM = PS006.PS001_PV_NUM
      ORDER BY DECODE(PS001.START_TIME,NULL,1,0), PS006.START_TIME_ACT;

   CURSOR C_NONPV IS
      SELECT PS006.CT058_MACHINE_ID, PS006.CT058_CT006_ID, PS006.DURATION,
             PS006.PS001_PV_NUM, PS006.START_TIME_ACT
        FROM PS006
       WHERE PS006.CT058_MACHINE_ID = '45'
         AND PS006.CT058_CT006_ID = 'BUR'
         AND PS006.START_TIME_ACT >= '15-AUG-01'
         AND PS006.PS001_PV_NUM IS NULL
      ORDER BY  PS006.START_TIME_ACT;

   V_PV       C_PV%ROWTYPE;
   V_NONPV    C_NONPV%ROWTYPE;
   V_SEQ      INTEGER := 0;

   GET_PV     BOOLEAN := TRUE;

   GET_NONPV BOOLEAN := TRUE;    PV_CURSOR_OPEN BOOLEAN := TRUE;
   NONPV_CURSOR_OPEN BOOLEAN := TRUE; BEGIN
   OPEN C_PV;
   OPEN C_NONPV;    LOOP
      IF GET_PV AND PV_CURSOR_OPEN THEN
         FETCH C_PV INTO V_PV;
      END IF;

      IF GET_NONPV AND NONPV_CURSOR_OPEN THEN
         FETCH C_NONPV INTO V_NONPV;
      END IF;

      EXIT WHEN C_PV%NOTFOUND AND C_NONPV%NOTFOUND;

      IF C_PV%NOTFOUND THEN
         CLOSE C_PV;
         PV_CURSOR_OPEN := FALSE;
      END IF;

      IF C_NONPV%NOTFOUND THEN
         CLOSE C_NONPV;
         NONPV_CURSOR_OPEN := FALSE;
      END IF;

      IF PV_CURSOR_OPEN AND NONPV_CURSOR_OPEN  THEN

          IF V_PV.START_TIME_ACT < V_NONPV.START_TIME_ACT THEN

             INSERT INTO PV_SCHED
             VALUES
                (V_SEQ,V_PV.CT058_MACHINE_ID, V_PV.CT058_CT006_ID,
                 V_PV.DURATION, V_PV.PS001_PV_NUM, V_PV.START_TIME_ACT);

             GET_PV := TRUE;
             GET_NONPV := FALSE;

         ELSE

             INSERT INTO PV_SCHED
             VALUES
                (V_SEQ,V_NONPV.CT058_MACHINE_ID, V_NONPV.CT058_CT006_ID,
                 V_NONPV.DURATION, V_NONPV.PS001_PV_NUM,
V_NONPV.START_TIME_ACT);
             GET_PV := FALSE;
             GET_NONPV := TRUE;

         END IF;

      ELSIF PV_CURSOR_OPEN AND NOT(NONPV_CURSOR_OPEN)  THEN

          INSERT INTO PV_SCHED
          VALUES

(V_SEQ,V_PV.CT058_MACHINE_ID, V_PV.CT058_CT006_ID,
V_PV.DURATION, V_PV.PS001_PV_NUM, V_PV.START_TIME_ACT); GET_PV := TRUE; GET_NONPV := FALSE; ELSIF NOT(PV_CURSOR_OPEN) AND NONPV_CURSOR_OPEN THEN INSERT INTO PV_SCHED VALUES
(V_SEQ,V_NONPV.CT058_MACHINE_ID, V_NONPV.CT058_CT006_ID,
V_NONPV.DURATION, V_NONPV.PS001_PV_NUM,
V_NONPV.START_TIME_ACT);
          GET_PV := FALSE;
          GET_NONPV := TRUE;

      END IF;

      V_SEQ := V_SEQ + 1;

   END LOOP;
END; When I try to run the program, the cursor goes to the following line:

      EXIT WHEN C_PV%NOTFOUND AND C_NONPV%NOTFOUND; and returns the following error:

   ORA-01001: Invalid Cursor ORA-06512: at line 47

I've looked up the error in the SQL Error Messages, It makes references to AREASIZE and MAXOPENCURSORS, but these values are set to a proper value . Any suggestions what I can do to clear this up?

Thanks,
Glenn Received on Thu Aug 23 2001 - 17:55:57 CEST

Original text of this message