SQL : TABLE DATA TYPE INTO Records

Here is the another new technique which I found in ORACLE sql today.

Following SQL helps to convert the SQL TABLEDATA type in to Rows.
SELECT * FROM THE
(SELECT str2tbl(str)
FROM DUAL)

str2tbl -- Procedure converts ; separated String into table.

CREATE OR REPLACE FUNCTION APPS.str2tbl( p_str IN VARCHAR2 ) return SRRDTABLETYPE
AS
l_str LONG DEFAULT p_str ';';
l_n NUMBER;
l_data SRRDTABLETYPE := SRRDTABLETYPE();
BEGIN
LOOP
l_n := INSTR( l_str, ';' );
EXIT WHEN (NVL(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := LTRIM(RTRIM(SUBSTR(l_str,1,l_n-1)));
l_str := SUBSTR( l_str, l_n+1 );
end LOOP;
RETURN l_data;
END;
/
This helps us to write SQL for tabledata types.