ORA-06511: PL/SQL Cursor already open
Solution 1
you're opening and closing the cursor in the loop. you should open and close it outside of the loop.
ie.
FETCH REG_CUR INTO V_PARENT;
DBMS_OUTPUT.PUT_LINE('----- Begin Output -----');
OPEN PAR_CUR; -- ************OPEN HERE
LOOP
IF V_REGIONNUMBER < 2 THEN
DBMS_OUTPUT.PUT_LINE('Line 1: (Region 1) ' || UPPER(V_CHILD));
DBMS_OUTPUT.PUT_LINE('Line 2: (Region 1) ' || UPPER(V_CHILD) || ' --> ' || '(Region 2) ' || UPPER (V_PARENT));
END IF;
V_PARENT2 := V_PARENT;
FETCH PAR_CUR INTO V_PARENT;
EXIT WHEN PAR_CUR%NOTFOUND;
V_REGIONNUMBER := V_REGIONNUMBER + 1;
IF V_REGIONNUMBER =2 THEN
DBMS_OUTPUT.PUT_LINE('Line 3: ' || '(Region 1) '|| UPPER(V_CHILD) || ' --> ' || '(Region 2) ' || UPPER(V_PARENT2) || ' --> ' || '(Region 3) ' || UPPER(V_PARENT));
ELSE
IF V_REGIONNUMBER =3 THEN
DBMS_OUTPUT.PUT_LINE('Line 4: ' || '(Region 1) '|| UPPER(V_CHILD) || ' --> ' || '(Region 2) ' || UPPER(V_PARENT3) || ' --> ' || '(Region 3) ' || UPPER(V_PARENT2)|| ' --> ' || '(Region 4) ' || UPPER(V_PARENT));
END IF;
END IF;
V_PARENT3 := V_PARENT2;
END LOOP;
CLOSE PAR_CUR;-- ************CLOSE HERE
as you were opening in the loop, on the 2nd iteration, the cursor errors as its already open
p.s. it looks like you're just trying to figure out the hierarchy and print it. if so you can just do this:
SQL> select ltrim(str, ' --> ') str
2 from (select child_name, level, SYS_CONNECT_BY_PATH('(Region ' || level || ') ' || child_name, ' --> ') str,
3 parent_name
4 from regions
5 start with child_name = 'Rockford'
6 connect by child_name = prior parent_name)
7 where parent_name is null
8 /
STR
--------------------------------------------------------------------------------
(Region 1) Rockford --> (Region 2) Illinois --> (Region 3) United States --> (Re
gion 4) North America
(Region 1) Rockford --> (Region 2) Texas --> (Region 3) United States --> (Regio
n 4) North America
SQL> select ltrim(str, ' --> ') str
2 from (select child_name, level, SYS_CONNECT_BY_PATH('(Region ' || level || ') ' || child_name, ' --> ') str,
3 parent_name
4 from regions
5 start with child_name = 'London'
6 connect by child_name = prior parent_name)
7 where parent_name is null
8 /
STR
--------------------------------------------------------------------------------
(Region 1) London --> (Region 2) England --> (Region 3) United Kingdom --> (Regi
on 4) Europe
SQL>
Solution 2
its (generally) recommended to use this syntax when opening any cursor
IF reg_cur %ISOPEN THEN
CLOSE reg_cur ;
END IF;
OPEN reg_cur ;
hope this will help ...
![RyanPatrick](https://i.stack.imgur.com/C7bZ9.jpg?s=256&g=1)
RyanPatrick
Business Analyst in Phoenix currently working through Master of Science, Information Systems, Database Administration at DePaul University. Thanks for your help in advance!
Updated on July 09, 2022Comments
-
RyanPatrick almost 2 years
Anyone have any ideas as to why the system is telling me that
PAR_CUR
is already open? Everything was working fine until I added the outer most cursor (dup_cur
) and now I'm getting this error. Thanks for any help you might have. The data is below as well.Error report:
ORA-06511: PL/SQL: cursor already open ORA-06512: at line 18 ORA-06512: at line 61 06511. 00000 - "PL/SQL: cursor already open" *Cause: An attempt was made to open a cursor that was already open. *Action: Close cursor first before reopening.
CODE:
ACCEPT p_1 prompt 'PLEASE ENTER THE REGION:' DECLARE v_child regions.child_name%TYPE := '&p_1'; v_parent regions.parent_name%TYPE; v_parent2 regions.child_name%TYPE; v_parent3 regions.child_name%TYPE; v_count NUMBER := 0; v_regionnumber NUMBER := 1; v_parentcount NUMBER := 0; v_dup regions.child_name%TYPE; CURSOR reg_cur IS SELECT Upper(parent_name) FROM regions WHERE Upper(child_name) = Upper(v_child) AND Upper(parent_name) = Upper(v_dup); CURSOR par_cur IS SELECT Upper(parent_name) FROM regions WHERE Upper(child_name) = v_parent AND parent_name IS NOT NULL; CURSOR dup_cur IS SELECT Upper(parent_name) FROM regions WHERE Upper(child_name) = Upper(v_child);
BEGIN OPEN dup_cur;
LOOP FETCH dup_cur INTO v_dup; EXIT WHEN dup_cur%NOTFOUND; SELECT Count(*) INTO v_count FROM regions WHERE Upper(child_name) = Upper(v_child); SELECT Count(parent_name) INTO v_parentcount FROM regions WHERE Upper(parent_name) = Upper(v_child); IF v_count > 0 OR v_parentcount > 0 THEN SELECT Count(parent_name) INTO v_count FROM regions WHERE Upper(child_name) = Upper(v_child); IF v_count > 0 THEN OPEN reg_cur; FETCH reg_cur INTO v_parent; dbms_output.Put_line('----- Begin Output -----'); LOOP IF v_regionnumber < 2 THEN dbms_output.Put_line('Line 1: (Region 1) ' || Upper(v_child)); dbms_output.Put_line('Line 2: (Region 1) ' || Upper(v_child) || ' --> ' || '(Region 2) ' || Upper (v_parent)); END IF; OPEN par_cur; v_parent2 := v_parent; FETCH par_cur INTO v_parent; EXIT WHEN par_cur%NOTFOUND; v_regionnumber := v_regionnumber + 1; IF v_regionnumber = 2 THEN dbms_output.Put_line('Line 3: ' || '(Region 1) ' || Upper(v_child) || ' --> ' || '(Region 2) ' || Upper(v_parent2) || ' --> ' || '(Region 3) ' || Upper(v_parent)); ELSE IF v_regionnumber = 3 THEN dbms_output.Put_line('Line 4: ' || '(Region 1) ' || Upper(v_child) || ' --> ' || '(Region 2) ' || Upper(v_parent3) || ' --> ' || '(Region 3) ' || Upper(v_parent2) || ' --> ' || '(Region 4) ' || Upper(v_parent)); END IF; END IF; CLOSE par_cur; v_parent3 := v_parent2; END LOOP; dbms_output.Put_line('----- End_Output -----'); CLOSE reg_cur; ELSE dbms_output.Put_line('----- Begin Output -----' || Chr(10) || 'Line 1: (Region 1) ' || Upper(v_child) || Chr(10) || '----- End_Output -----'); END IF; ELSE dbms_output.Put_line('----- Begin Output -----' || Chr(10) || Upper(v_child) ||' is not in the table.' || Chr(10) || '----- End_Output -----'); END IF; END LOOP; CLOSE dup_cur; END; CREATE TABLE regions ( PARENT_NAME VARCHAR2(30), CHILD_NAME VARCHAR2(30) ); INSERT INTO regions VALUES('Texas','Rockford'); INSERT INTO regions VALUES('Colorado','Aurora'); INSERT INTO regions VALUES(NULL,'Asia'); INSERT INTO regions VALUES(NULL,'Australia'); INSERT INTO regions VALUES(NULL,'Europe'); INSERT INTO regions VALUES(NULL,'North America'); INSERT INTO regions VALUES('Asia','China'); INSERT INTO regions VALUES('Asia','Japan'); INSERT INTO regions VALUES('Australia','New South Wales'); INSERT INTO regions VALUES('New South Wales','Sydney'); INSERT INTO regions VALUES('Canada','Ontario'); INSERT INTO regions VALUES('China','Beijing'); INSERT INTO regions VALUES('England','London'); INSERT INTO regions VALUES('Europe','United Kingdom'); INSERT INTO regions VALUES('Illinois','Aurora'); INSERT INTO regions VALUES('Illinois','Chicago'); INSERT INTO regions VALUES('Illinois','Rockford'); INSERT INTO regions VALUES('Wisconsin','Madison'); INSERT INTO regions VALUES('Japan','Osaka'); INSERT INTO regions VALUES('Japan','Tokyo'); INSERT INTO regions VALUES('North America','Canada'); INSERT INTO regions VALUES('North America','United States'); INSERT INTO regions VALUES('Ontario','Ottawa'); INSERT INTO regions VALUES('Ontario','Toronto'); INSERT INTO regions VALUES('United States','Colorado'); INSERT INTO regions VALUES('United States','Illinois'); INSERT INTO regions VALUES('United States','Texas'); INSERT INTO regions VALUES('United Kingdom','England'); COMMIT;