Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

nested cursor loops

Posted on 2004-08-31
2
Medium Priority
?
982 Views
Last Modified: 2012-05-05
I have a problem in that i need to create a nested loop in PL/SQL. I basically need to use information from the first loop which uses a cursor to select information in the second loop, which also uses a cursor. As it stands i can not get it working. Any help would be appreciated, especially examples.

Cheers
0
Comment
Question by:wizard2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 1000 total points
ID: 11942255
You need to use a paramterized cursor, like this:


declare
   cursor outer is
     select col1
        from tab1;
   cursor inner(v_col1 tab1.col1%type)
     select col2
        from tab2
       where col3 = v_col1;

begin

   for outer_rec in outer loop
     for inner_rec in inner(outer_rec.col1) loop
       ...
     end loop;
  end loop;
end;
0
 
LVL 7

Expert Comment

by:grim_toaster
ID: 11942282
Here's a very simple example of using an inner and outer cursor for loop:

BEGIN
FOR rec1 IN (SELECT table_name FROM user_tables) LOOP
      FOR rec2 IN (SELECT column_name FROM user_tab_columns WHERE table_name = rec1.table_name AND column_id = 1) LOOP
            DBMS_OUTPUT.put_line(rec2.column_name);      
      END LOOP;
END LOOP;
END;

If you have any particular problems, perhaps we could help you with them?
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

618 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