PL/SQL recursive lookup with cursor

Question

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;
/

Answer 1

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 = &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

• ypercubeᵀᴹ: The query looks fine but the data in your question do not match with the output (rows 1,2,3,5).
• Vijay: Sorry I didn't copy all the data. The data I have is huge, the relevant rows are here 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
• Colin 't Hart: Instead of a comment, please edit your question and/or answer @Vijay.