Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news.glorb.com!postnews1.google.com!not-for-mail
From: mromano@sensis.com (Michelle Romano)
Newsgroups: comp.databases.oracle.tools
Subject: Re: Seeking recursive solution to replace nested for-loops
Date: 3 Mar 2004 05:55:49 -0800
Organization: http://groups.google.com
Lines: 67
Message-ID: <aaba7f35.0403030555.52b727fb@posting.google.com>
References: <aaba7f35.0402190919.31edd67f@posting.google.com> <GOxZb.9939$23.8686@lakeread04> <aaba7f35.0403010548.61f136b6@posting.google.com> <8244b794.0403011953.54f798d6@posting.google.com> <aaba7f35.0403020607.1d4ea02e@posting.google.com> <8244b794.0403022131.54687aa8@posting.google.com>
NNTP-Posting-Host: 24.213.139.206
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1078322149 16610 127.0.0.1 (3 Mar 2004 13:55:49 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 3 Mar 2004 13:55:49 +0000 (UTC)
Xref: newssvr20.news.prodigy.com comp.databases.oracle.tools:84924

> 
> Ah...I see. Would you mind posting your current nested-loop solution
> as a starting point?
> 
> Thanks,
> Dave

Not at all...here it is.  Note that this is a scaled down version,
which will basically generate each combination via parm_list.  This
version will just display the pair combinations (using put_text).  You
will notice that I need to comment out the outer loop to prevent
generating individual parameters.  That is one of the issues with the
nested for-loop solution.  Within the loop, I also run a function
against each combination generated (normally resides where I've added
the comment "Run function against parm list here").  My goal is to
create a function to return combination values to replace parm_list
values currently generated by the for-loop.

CREATE OR REPLACE procedure run_combo is

parm_count pls_integer := 0;
parm_list  varchar2(500);

Type parmArrayType is table of varchar2(100)
      index by pls_integer;
parm_array parmArrayType;
cursor ptx_cur is
  select parm
    from parm_table_xref 
   order by parm;

ptx_rec ptx_cur%rowtype;
   
begin
   open ptx_cur;
  
   loop
      fetch ptx_cur into ptx_rec;
	   exit when (ptx_cur%notfound);
   
      parm_count := parm_count + 1;
      parm_array(parm_count) := ptx_rec.parm;
      
   end loop;
     
   close ptx_cur;
  
   for i in 1..parm_count loop
  
--      parm_list := parm_array(i);         
--      put_text(parm_list);
         
-- Run function against parm list here --
           
      for j in i+1..parm_count loop -- parameter pairs 
          
         parm_list:= parm_array(i)||', '||parm_array(j);
         put_text(parm_list);
         
-- Run function against parm list here --       
   
      end loop; -- j loop 
    
   end loop; -- i loop  
  
end run_combo;
/