ORA-06511: PL/SQL Cursor already open

问题:

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;

回答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>

回答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 ...