SQL & PL/SQL

Hello,
I am traversing a table recursively in a pl/sql function returning a table. I created the function which works but returning a VARCHAR2 now and I want to transform it returning a table.
My question is if that variable STR was a table how could that be implemented.

create or replace
FUNCTION TRAVERSE_STR(PART IN NUMBER)
RETURN
VARCHAR2
IS

STR VARCHAR2(4000);
TYPE PART_HAS_SUBPART_ROW IS TABLE OF PART_HAS_SUBPART%ROWTYPE INDEX BY BINARY_INTEGER;
PART_SUB_PART PART_HAS_SUBPART_ROW;
STATE NUMBER;
CURSOR CUR IS SELECT * FROM PART_HAS_SUBPART WHERE PART_ID=PART;
I BINARY_INTEGER := 0;
BEGIN
OPEN CUR;
LOOP
I := I+1;
FETCH CUR INTO PART_SUB_PART(I);
EXIT WHEN CUR%NOTFOUND;
SELECT IS_HAS_PART INTO STATE FROM PART WHERE ID = PART_SUB_PART(I).SUB_PART_ID;
IF STATE=0 THEN
STR := STR || PART_SUB_PART(I).PART_ID || ' ' ||PART_SUB_PART(I).SUB_PART_ID || ' ';
ELSE IF STATE=1 THEN
STR := str || TRAVERSE_STR(PART_SUB_PART(I).SUB_PART_ID) || ' ';
END IF;
END IF;
END LOOP;
CLOSE CUR;
RETURN STR;
END TRAVERSE_STR;
======================================
I don't know if it is clear but that is my variant with table:

create or replace
FUNCTION TRAVERSE_TBL(PART IN NUMBER)
RETURN
PART_HAS_SUBPART_TYPE
IS

TYPE PART_HAS_SUBPART_ROW IS TABLE OF PART_HAS_SUBPART%ROWTYPE INDEX BY BINARY_INTEGER;
PART_SUB_PART PART_HAS_SUBPART_ROW;
RETURN_TABLE PART_HAS_SUBPART_TYPE;
STATE NUMBER;
CURSOR CUR IS SELECT * FROM PART_HAS_SUBPART WHERE PART_ID=PART;
I BINARY_INTEGER := 0;
BEGIN

OPEN CUR;
LOOP
I := I+1;
FETCH CUR INTO PART_SUB_PART(I);
EXIT WHEN CUR%NOTFOUND;
SELECT IS_HAS_PART INTO STATE FROM PART WHERE ID = PART_SUB_PART(I).SUB_PART_ID;
IF STATE=0 THEN
RETURN_TABLE.EXTEND;
RETURN_TABLE(RETURN_TABLE.COUNT) := PART_ROW(PART_SUB_PART(I).PART_ID, PART_SUB_PART(I).SUB_PART_ID, PART_SUB_PART(I).AMOUNT);

ELSE IF STATE=1 THEN
RETURN_TABLE.EXTEND;
RETURNTABLE(RETURN_TABLE.COUNT) := SELECT TRAVERSE_TBL(PART_SUB_PART(I).SUB_PART_ID) FROM DUAL;_

END IF;
END IF;
END LOOP;
CLOSE CUR;
RETURN RETURN_TABLE;
END TRAVERSE_TBL;

Thank you in advance!

Edited by: Todor Kolev on Feb 26, 2012 7:46 PM