Geek Answers Handbook

ORA-06511: PL / SQL cursor is already open

Does anyone have any ideas as to why the system tells me that PAR_CUR already open? Everything worked fine until I added an external cursor ( dup_cur ) and now I get this error. Thanks for any help you could get. The data is also lower.

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

START 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;
+6
source share
2 answers

you open and close the cursor in a loop. you have to open and close it outside the loop.

t

 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

when you opened in the loop, in the second iteration, the cursor errors are already open

ps it looks like you're just trying to figure out a hierarchy and print it. if so, you can simply 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>
+8
source

its (usually) recommended to use this syntax when opening any cursor

 IF reg_cur %ISOPEN THEN CLOSE reg_cur ; END IF; OPEN reg_cur ;

hope this helps ...

+5
source

More articles:


All Articles