create or replace function rotate_parts_func_jp(v_ename varchar2)
return varchar2 as
/*****************************************************
Author JP Vijaykumar, Oracle DBA, 
Date Sep 30th 2007
This function takes one argument employee name
*****************************************************/
v_sname varchar2(1000):='';
begin
for c1 in (select ename from scott.emp
               where mgr in (select empno from scott.emp
               where ename = trim(v_ename))) loop
if (v_sname IS NULL) then
    v_sname:=trim(c1.ename);
else
   v_sname:=v_sname||'|'||trim(c1.ename);
end if;
end loop;
return v_sname;
end;



set serverout on size 1000000
declare
v_emp 	varchar2(1000);
v_emq	varchar2(1000);
v_str	varchar2(1000);
v_sts	varchar2(1000);
v_stt	varchar2(1000);
begin
/* select ename from scott.emp connect by prior empno = mgr start with ename='KING'; 'SMITH'; */
v_emq:='KING'; --'SMITH';
while (nvl(length(v_emq),0) > 0)  loop
       if (length(v_emp)>0) then
       v_emp:=v_emp||'|'||v_emq;
       else
       v_emp:=v_emq;
       end if;       
       select rotate_parts_func_jp(v_emq) into v_sts from dual;
     
 	if (length(v_sts) > 0) then
        if (length(v_str) > 0) then
        v_str:=trim(v_str)||'|'||trim(v_sts);
        else
        v_str:=trim(v_sts);
        end if;
        end if;
       
	select substr(v_str,1,instr(v_str,'|') -1) into v_emq from dual;
        if (nvl(length(v_emq),0) = 0) then
        v_emp:=v_emp||'|'||v_str;
        end if;       
               
       if (instr(v_str,'|') > 1) then
       select substr(v_str,instr(v_str,'|') +1,length(v_str)) into v_stt from dual;
        
       v_str:=v_stt; 
       end if;
       if (substr(v_str,length(v_str),length(v_str)) = '|') then
       	v_str:=substr(v_str,1,length(v_str) -1);
       end if;
       if (substr(v_str,1,1) = '|') then
       	v_str:=substr(v_str,2,length(v_str));
       end if;    
             
end loop;
select decode(substr(v_emp,length(v_emp),length(v_emp)),'|',substr(v_emp,1,length(v_emp) -1),v_emp) into v_emp from dual;
dbms_output.put_line(v_emp);
end;

KING|BLAKE|JONES|CLARK|TURNER|ALLEN|WARD|JAMES|MARTIN|FORD|SCOTT|MILLER|
SMITH|ADAMS

The following query displays the same output in a single column. I am presenting the output for comparison of the results.

SQL> select ename from scott.emp connect by prior empno = mgr start with ename='KING';

ENAME
----------
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER

14 rows selected.