Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01001: invalid cursor
ORA-01001: invalid cursor [message #601800] Mon, 25 November 2013 23:35 Go to next message
mist598
Messages: 867
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,


If i close cursor after a loop and i am getting o/p but the erro'r shows ORA-01001: invalid cursor

 1  declare
 2     CURSOR c1(v_num number) IS SELECT a.order_number v1, a.flow_status_code v2,b.line_id v3,
 3                                b.ordered_item v4, b.source_type_code v5, b.flow_status_code v6
 4                                FROM
 5                                oe_order_headers_all a,
 6                                oe_order_lines_all b
 7           WHERE a.header_id=b.header_id
 8           AND a.order_number=v_num;
 9  BEGIN
10   dbms_output.put_line('ordernumber'||' '||'flowstatus'||'  '||'lineid'||'  '||'itemname'||' '||'linetype'||'||'linestatus');
11  dbms_output.put_line('-----------'||' '||'----------'||'  '||'------'||'  '||'--------'||'  '||'--------'||'  '||'----------');
12     FOR v_var IN c1(59219) LOOP
13      EXIT WHEN c1%notfound;
14     dbms_output.put_line( v_var.v1||'       '||v_var.v2||'      '||v_var.v3||'  '||v_var.v4||'   '||v_var.v
15     dbms_output.put_line('Count is:-'||c1%rowcount);
16     end loop;
17     CLOSE c1;  -->Here
18*    end;


output:

ordernumber flowstatus  lineid  itemname  linetype  linestatus
----------- ----------  ------  --------  --------  ----------
59219       CLOSED      236756  XP9007   INTERNAL      CLOSED
Count is:-1

declare
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 17


if i comment on line 17 close c1; the output is working fine.Can any one suggest me on this and is this mandatory to close a cursor, if i not close what happens on my code.

Thank you
Mist





Re: ORA-01001: invalid cursor [message #601802 is a reply to message #601800] Mon, 25 November 2013 23:36 Go to previous messageGo to next message
BlackSwan
Messages: 22535
Registered: January 2009
Senior Member
cursor no longer exists outside the LOOP
Re: ORA-01001: invalid cursor [message #601803 is a reply to message #601802] Mon, 25 November 2013 23:40 Go to previous messageGo to next message
mist598
Messages: 867
Registered: February 2013
Location: Hyderabad
Senior Member
Hi BlackSwan,

Thank you for early reply, i tried to close cursor inside a loop i am getting same error.Can you please suggest me

Thank you
Mist
Re: ORA-01001: invalid cursor [message #601805 is a reply to message #601803] Mon, 25 November 2013 23:41 Go to previous messageGo to next message
BlackSwan
Messages: 22535
Registered: January 2009
Senior Member
If you do not OPEN cursor, then you should not CLOSE cursor.
Re: ORA-01001: invalid cursor [message #601806 is a reply to message #601805] Mon, 25 November 2013 23:43 Go to previous messageGo to next message
mist598
Messages: 867
Registered: February 2013
Location: Hyderabad
Senior Member
Thank you very much BlackSwan,


Regards,
Mist
Re: ORA-01001: invalid cursor [message #601815 is a reply to message #601806] Tue, 26 November 2013 00:14 Go to previous message
Littlefoot
Messages: 19335
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Cursor FOR loop does many tasks for you.

If you used the oldfashioned loop, then your code might have looked like
declare
  cursor c1 is (select ... from ...)
  c1r c1%rowtype;
begin
  open c1;
  loop
    fetch c1 into c1r;
    exit when c1%notfound;
    
    <do something here>
  end loop;
  close c1;
end;

With a cursor FOR loop, all you need is
for c1r in (select ... from ...) loop
  <do something here>
end loop;


As you can see, option you chose doesn't include OPEN, FETCH, EXIT, CLOSE.
Previous Topic: unix time to date
Next Topic: would i be able to run this query
Goto Forum:
  


Current Time: Wed Jul 30 14:57:53 CDT 2014

Total time taken to generate the page: 0.10583 seconds