PL/SQL recursive lookup with cursor I basically have a need for recursing through data in my Oracle table so I can find related rows and send them out. It is a single table, a single ANSI SQL query seems not possible due to turin completion I need in this logic - perhaps a PL/SQL function. 

I have a table that contains DNS data looks like below

RN TIMESTAMP  REQUEST                              TYPE  RESPONSE
1  1431606892 www.microsoft.com                    CNAME toggle.www.ms.akadns.net.
2  1431606892 www.ms.akadns.net                    CNAME www.microsoft.com-c.edgekey.net.globalredir.akadns.net.
3  1431606892 c.edgekey.net.globalredir.akadns.net A     23.6.72.154
4  1431606892 www.google.com                       A     173.194.121.48
5  1431606892 c.edgekey.net.globalredir.akadns.net A     23.6.72.194


I want to write a PL/SQL function that when a person searches for REQUEST "www.microsoft.com" it returns rows 1,2,3,5 to the user.  Basically it follows the CNAME trail (up to 255 times)

CREATE OR REPLACE PACKAGE DNSUTLS AS
TYPE col_table is table of tfinal%ROWTYPE;
FUNCTION CNAME_TRAIL(nsearch VARCHAR2) RETURN col_table pipelined;
END DNSUTLS;
/
CREATE OR REPLACE PACKAGE BODY DNSUTLS AS
DUMMY NUMBER;
FUNCTION CNAME_TRAIL(nsearch VARCHAR2) RETURN 
col_table pipelined as 
cursor temp_cur is
       SELECT * FROM tfinal where request=lower(search);
BEGIN
   for cur_rec in temp_cur loop
--if cur_rec.type = 'CNAME' THEN 
--keep recursing to add additional rows
--endif;
      pipe row(cur_rec)
   end loop;
END;
END;

/
The answer is really use Hierarchical query model from Oracle. http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm

So in my case:

select * 
from tfinal 
start with request = &amp;search 
       and type = 'CNAME' 
connect by prior response = request;


The solution is simpler than what I envisioned.  Here is the real data and the query itself

select a.request,a.response,level from dnstest a  left join dnstest b on b.request=a.response||'.'  start with a.request='www.microsoft.com' connect by prior a.response=a.request||'.';


The data looks like 

1,1431606892,www.microsoft.com,CNAME,toggle.www.ms.akadns.net.
2,1431606892,toggle.www.ms.akadns.net,CNAME,www.ms.akadns.net.
3,1431606892,www.ms.akadns.net,CNAME,www.microsoft.com-c.edgekey.net.globalredir
.akadns.net.
4,1431606892,www.microsoft.com-c.edgekey.net.globalredir.akadns.net,CNAME,c.edge
key.net.globalredir.akadns.net.
5,1431606892,c.edgekey.net.globalredir.akadns.net,A,23.6.72.154
6,1431606892,www.google.com,A,173.194.121.48
7,1431606892,c.edgekey.net.globalredir.akadns.net,A,23.6.72.194