Menu
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;
763
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>
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 ...
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
