Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06511: PL/SQL: cursor already open (10g , windows XP SP2)
icon5.gif  ORA-06511: PL/SQL: cursor already open [message #305541] Tue, 11 March 2008 04:19 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

I made that code :-

  1  DECLARE
  2    CURSOR emp_cursor IS
  3     SELECT employee_id, last_name FROM employees
  4     WHERE department_id =30;
  5  BEGIN
  6  if not emp_cursor%isopen then open emp_cursor;
  7  else null;
  8  end if;
  9     FOR emp_record IN emp_cursor
 10      LOOP
 11       DBMS_OUTPUT.PUT_LINE
 12  ( emp_record.employee_id  ||' ' ||emp_record.last_name);
 13      END LOOP;
 14  close emp_cursor;
 15* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at line 3
ORA-06512: at line 9


I don't know where the wrong here ?!!!
Re: ORA-06511: PL/SQL: cursor already open [message #305543 is a reply to message #305541] Tue, 11 March 2008 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FOR statement open and close the cursor for you. You don't need to do it.

Regards
Michel
Re: ORA-06511: PL/SQL: cursor already open [message #305544 is a reply to message #305541] Tue, 11 March 2008 04:26 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
read about implict and explicit cursor and you will know why you are getting the error.comment the 6th and 14th line and see what you get.

regards,
Re: ORA-06511: PL/SQL: cursor already open [message #305556 is a reply to message #305541] Tue, 11 March 2008 04:56 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Besides the point noted before, you should read up on scope.
How can cursor emp_cursor ever be open right after the BEGIN?
Since emp_cursor is declared locally, it ceases to exist as soon as you leave the procedure.
SQL> create or replace package test
  2  as
  3    procedure callee;
  4    procedure caller;
  5  end;
  6  /

Package created.

SQL> create or replace package body test
  2  as
  3    procedure callee
  4    is
  5      cursor c
  6      is
  7        select 1
  8        from   dual;
  9    begin
 10      open c;
 11    end callee;
 12
 13    procedure caller
 14    is
 15    begin
 16      callee;
 17      callee;
 18    end caller;
 19  end;
 20  /

Package body created.

SQL> exec test.caller

PL/SQL procedure successfully completed.

See? No cursor-already-open on the second call.
Previous Topic: What's wrong with this:PLS-00320
Next Topic: How to Drop invalid synonyms
Goto Forum:
  


Current Time: Thu Dec 08 02:28:04 CST 2016

Total time taken to generate the page: 0.25718 seconds