Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Cursor with the same name (Oracle 9i)
Multiple Cursor with the same name [message #440963] Thu, 28 January 2010 02:10 Go to next message
MiteshGaur
Messages: 1
Registered: January 2010
Location: Mumbai, India
Junior Member
Hello,
This is my first post to the forum. Thanks to all who have helped many of us in solving their problems.

Today i have one query related to cursor, please let me know your thoughts on it -

I created a cursor named c_cursor1, i opened it and fetch few records and closed it successfully. Can i reopen it?
Next question is - can i create an another cursor with the same name 'c_cursor1' (note - previous has successfully closed)?

Thanks in advance!!!

-- Micky
Re: Multiple Cursor with the same name [message #440964 is a reply to message #440963] Thu, 28 January 2010 02:16 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
MiteshGaur wrote on Thu, 28 January 2010 08:10
Hello,
I created a cursor named c_cursor1, i opened it and fetch few records and closed it successfully. Can i reopen it?

You could have just tried that - yes you can.
MiteshGaur wrote

Next question is - can i create an another cursor with the same name 'c_cursor1'

Not in the same scope no
Re: Multiple Cursor with the same name [message #441218 is a reply to message #440963] Fri, 29 January 2010 09:07 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
It is possible to declare multiple cursors with the same name if there specification is different.

However it is meaningless because when you try to use them Oracle will gaff on them.

Kevin

SQL> declare
  2     cursor c1 is select * from dual;
  3     cursor c1 (in_1_p in number) is select * from dual;
  4     cursor c1 (in_1_p in number, in_2_p in number) is select * from dual;
  5  begin null;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> declare
  2     cursor c1 is select * from dual;
  3     cursor c1 (in_1_p in number) is select * from dual;
  4     cursor c1 (in_1_p in number, in_2_p in number) is select * from dual;
  5  
  6     v1 varchar2(1);
  7  begin null;
  8     open c1;
  9     open c1 (1);
 10     open c1 (1,2);
 11  end;
 12  /
   open c1;
   *
ERROR at line 8:
ORA-06550: line 8, column 4:
PLS-00307: too many declarations of 'C1' match this call
ORA-06550: line 8, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 9:
PLS-00307: too many declarations of 'C1' match this call
ORA-06550: line 9, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 10, column 9:
PLS-00307: too many declarations of 'C1' match this call
ORA-06550: line 10, column 4:
PL/SQL: SQL Statement ignored
Previous Topic: Fun With Triggers and Mutating tables
Next Topic: To select specific columns from tables
Goto Forum:
  


Current Time: Mon Sep 26 04:19:34 CDT 2016

Total time taken to generate the page: 0.13279 seconds