Re: ORA-01001 Invalid Cursor error

"Glenn Griffith" <gagriff_at_bellsouth.net> wrote in message news: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
>

the cursor has already been closed when the statement is executed. i'd suggest you close the cursors outside of the loop, and getting rid of the local booleans and redundant statements.

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

  SELECT PS006.CT058_MACHINE_ID
       , PS006.CT058_CT006_ID
       , PS006.DURATION
       , PS006.PS001_PV_NUM
       , PS006.START_TIME_ACT

   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

  v_pv     c_pv%ROWTYPE;
  v_nonpv  c_nonpv%ROWTYPE;
  v_seq    INTEGER := 0;

BEGIN
  OPEN c_pv;
  OPEN c_nonpv;
  FETCH c_pv INTO v_pv;
  FETCH c_nonpv INTO v_nonpv;
  LOOP
    EXIT WHEN c_pv%NOTFOUND AND c_nonpv%NOTFOUND;

    IF (c_pv%FOUND AND c_nonpv%FOUND AND
        v_pv.START_TIME_ACT < v_nonpv.START_TIME_ACT)
    OR (c_nonpv%NOTFOUND) 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);

      FETCH c_pv INTO v_pv;

    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);

      FETCH c_nonpv INTO v_nonpv;

    END IF;
    v_seq := v_seq + 1;
  END LOOP;
  CLOSE c_pv;
  CLOSE c_nonpv;
END; the routine would be even more straightforward if you could combine the two selects using a UNION ALL operator, including appropriate columns to enable a proper ORDER BY...

it looks as if START_TIME_ACT may be of datatype DATE, in which case i would strongle recommend that the literal string '15-AUG-01' be converted to DATE before the comparison... i.e.

      AND PS006.START_TIME_ACT >= TO_DATE('15-AUG-01','DD-MON-YY') HTH Received on Sat Aug 25 2001 - 05:25:01 CEST