Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Is it possible to 'concatenate' tables in PL/SQL

916833Feb 26 2012 — edited Mar 3 2012
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
This post has been answered by Frank Kulash on Feb 26 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 31 2012
Added on Feb 26 2012
15 comments
849 views