Oracle string Split

Oracle string Split 1. CREATE an array SQL code create or replace type T_RET_TABLE is table of VARCHAR2 (512) 2. CREATE a string segmentation FUNCTION SQL code CREATE OR REPLACE FUNCTION F_SPLIT_STRING (AS_STR VARCHAR2, AS_SPLIT VARCHAR2) RETURN T_RET_TABLE IS -- Author: Guomm -- Created: 13:38:46 -- Purpose: string segmentation -- Params -- AS_STR: string to be processed -- AS_SPLIT: String delimiter V_OUT T_RET_TABLE; v_TMP VARCHAR2 (4000); V_ELEMENT VARCHAR2 (4000); BEGIN V_TMP: = AS_STR; V_OUT: = T_RET_TABLE (); -- if a matched delimiter exists while instr (V_TMP, AS_SPLIT)> 0 LOOP V_ELEMENT: = SUBSTR (V_TMP, 1, INSTR (V_TMP, AS_SPLIT)-1); V_TMP: = SUBSTR (V_TMP, INSTR (V_TMP, AS_SPLIT) + LENGTH (AS_SPLIT ), LENGTH (V_TMP); V_OUT.EXTEND (1); V_OUT (V_OUT.COUNT): = V_ELEMENT; end loop; IF V_TMP is not null then V_OUT.EXTEND (1); V_OUT (V_OUT.COUNT ): = V_TMP; end if; RETURN V_OUT; END F_SPLIT_STRING; OK, this function has been implemented. 3. Call the SQL code SELECT * FROM TABLE (F_SPLIT_STRING ('1, 2, 3, 4, 5, 6', ',') DECLARE V_ARRAY T_RET_TABLE; BEGIN V_ARRAY: = F_SPLIT_STRING ('1, 2, 3, 4, 5, 6 ','); for I IN 1 .. v_ARRAY.COUNT LOOP DBMS_OUTPUT.PUT_LINE (V_ARRAY (I); END LOOP; END;