Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sqlplus nested loop problem (Urgent please!)

Posted on 2004-09-17
2
Medium Priority
?
837 Views
Last Modified: 2008-01-16
The nested loop is not working!!!
The outer loop is executing only once.Inner loop is executing fine...
What is the problem??
=============
declare
v1 VARCHAR2(10);
v2 VARCHAR2(30);
v3 integer;
v4 integer;
cursor c1 is
        select f1 from tab1;
cursor c2 is
        select f2 from tab2;

begin
open c1;
open c2;
<<loop1>>
loop
fetch c1 into v1;
exit when c1%NOTFOUND;
<<loop2>>
loop
fetch c2 into v2;
exit when c2%NOTFOUND;
insert into tab3(ID, FAMILY) values (v1, v2);

end loop loop2;
end loop loop1;
close c1;
close c2;
end;
/
====================
0
Comment
Question by:tooki
2 Comments
 
LVL 7

Accepted Solution

by:
BobMc earned 600 total points
ID: 12089102
You open both cursors at the top and fetch rows from them - first time around the outer loop, this is fine.
Second time around, you have already exhausted c2, so it will always return no more rows.

You need to close c2 and reopen it for each inner loop

declare
v1 VARCHAR2(10);
v2 VARCHAR2(30);
v3 integer;
v4 integer;
cursor c1 is
        select f1 from tab1;
cursor c2 is
        select f2 from tab2;

begin
open c1;
<<loop1>>
loop
  fetch c1 into v1;
  exit when c1%NOTFOUND;
  <<loop2>>
  open c2;
  loop
    fetch c2 into v2;
    exit when c2%NOTFOUND;
    insert into tab3(ID, FAMILY) values (v1, v2);
  end loop loop2;
  close c2;
end loop loop1;
close c1;
end;
/
0
 

Author Comment

by:tooki
ID: 12089227
Many thanks!!!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question