recursive procedure problem
create or replace package testa
as
llist varchar2(500):=null;
procedure test123(pid in number);
end;
create or replace package body testa
as
procedure test123(pid number)
as
--parent_id number:=10;
chid number;
ip number;
isparent number;
llist varchar2(500);
cursor test(pid number) is
select a.qtype_id CHID, a.is_parent
from parentchild a
where a.parent_id = PID;
BEGIN
FOR I IN TEST(pid)
LOOP
--DBMS_OUTPUT.PUT_LINE(I.CHID || ', ' || I.IS_PARENT);
llist:=llist||','||I.CHID;
IF I.IS_PARENT =1 THEN
test123(i.chid);
end if;
END LOOP;
dbms_output.put_line(llist);
end;
end;
i am passing i/p as 9 .
for 9 -->10,14,17 are childs.
for 10-->11,12 are childs.
for 14-->15,16 are childs
for 17-->20,18,19 are childs
the procedure o/p is getting as
,11,12
,15,16
,20,18,19
,10,14,17
but i need o/p as
10,14,17,11,12,,15,16,20,18,19
how to do it