creating a function for finding ancestor in oracle from parent-child table

when we have a table as

parent        child

A            B
B            C
C           D


then here the ancestor of (D) is to be found as
C
B
A

Thus finding such result from the table can be easily done in database through the use of hierarchial query

SELECT parent FROM ancestor START WITH child = 'D' CONNECT BY PRIOR parent = child;

this query can directly qive us the result as oracle has the feature of hierarchical query . So The function in pl/sql can be written as:

create or replace FUNCTION find_ancestor(in_cname IN varchar2) 
return varchar2

AS 
    stat varchar2(50);


CURSOR anc_cur IS 
SELECT parent FROM ancestor START WITH child = in_cname CONNECT BY PRIOR parent = child;
anc_rec anc_cur%ROWTYPE;
indx number := 0;

BEGIN
open anc_cur;

dbms_output.put_line('-------ancestors  of ----------' || in_cname);
loop
fetch anc_cur into anc_rec;

if anc_cur%NOTFOUND
then
exit;
else
   -- return anc_rec.parent;
dbms_output.put_line(''||anc_rec.parent);


end if;
end loop;
close anc_cur;
stat := '-----success---------';
return stat;


END find_ancestor;

/

now the result can be obtained with query  as 
select find_ancestor('D') from dual;


now we can also write the function without using this in built query but using recursion and cursor as:

create or replace FUNCTION find_ancestor(in_cname IN varchar2) 
return varchar2

AS 
    stat varchar2(50);


CURSOR anc_cur IS 
SELECT parent FROM ancestor  where child = in_cname;
anc_rec anc_cur%ROWTYPE;
indx number := 0;

BEGIN
open anc_cur;

-- dbms_output.put_line('-------ancestors  of ----------' || in_cname);
loop
fetch anc_cur into anc_rec;

if anc_cur%NOTFOUND
then

            -- dbms_output.put_line('inside cursor exit');
exit;
else

         stat:= find_ancestor(anc_rec.parent);
   -- return anc_rec.parent;
dbms_output.put_line(''||anc_rec.parent);



end if;
end loop;

close anc_cur;
    stat:= '----success---';

return stat;


END find_ancestor;
/

here the same query can bewritten and the result is same but here we have used recursion to find the parent.