Oracle will provide a read-consistent view of the data. Assuming that you're using the default transaction isolation level of read committed, the data that a query will return is fixed at the point in time that the cursor is opened (whether the cursor is implicit or explicit is irrelevant).
The only exception to this is that PL/SQL that is called by the query (i.e. stored functions) will see the data as it exists at the point in time that the function is called not at the point that the cursor was opened.
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
Best Answer
There are probably better ways to achieve your underlying goal, but if you really want values from dynamic SQL into a strongly typed refcursor I guess you could do something like this: