Home » SQL & PL/SQL » SQL & PL/SQL » Using cursor within a cursor
Using cursor within a cursor [message #261408] Wed, 22 August 2007 09:18 Go to next message
abhitabh.jamankar
Messages: 4
Registered: August 2007
Location: India
Junior Member
OS WINDOWS Server 2003
Database Oracle 10gR2

Hi
can we use a cursor within another cursor.
How?
 cursor c1 is
 select * from dummy where {.....};
 begin
  for transrec in c1
 loop
    for trec in c1
    loop
       {.......}
    end loop;
 end loop;
close c1;

Above code showing the error
ORA-06511: PL/SQL: cursor already open

[Updated on: Wed, 22 August 2007 10:17] by Moderator

Report message to a moderator

Re: Using cursor within a cursor [message #261411 is a reply to message #261408] Wed, 22 August 2007 09:23 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

You can't be able to open same cursor twice into the internal loop. For that defind the one more cursor with the same query and use that one. For example:

declare
cursor c1 is
select * from dummy where {.....};
cursor c2 is
select * from dummy where {.....};
begin
for transrec in c1
loop
for trec in c2
loop
{.......}
end loop;
end loop;
end;


Thumbs Up Cheers
Soumen

[Updated on: Wed, 22 August 2007 09:24]

Report message to a moderator

Re: Using cursor within a cursor [message #261571 is a reply to message #261411] Thu, 23 August 2007 01:20 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That seems a bit of a strange logic. What are you trying to do? Why would you need the inner loop? There's a fair chance that this can be done in another way...

You could fetch bulk collect the data in a collection once and loop through the collection twice. But still...

MHE
Previous Topic: FORALL statement error while compiling
Next Topic: How to decrypt data in Oracle (Merged)
Goto Forum:
  


Current Time: Thu Dec 08 08:44:33 CST 2016

Total time taken to generate the page: 0.14607 seconds