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>
GET_NONPV BOOLEAN := TRUE; PV_CURSOR_OPEN BOOLEAN := TRUE;
NONPV_CURSOR_OPEN BOOLEAN := TRUE; BEGIN
OPEN C_PV;
OPEN C_NONPV; LOOP
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 VALUESV_NONPV.START_TIME_ACT);
(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,
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