ORACLE CUSTOM FUNCTION - SPLIT DELIMITER STRING

-- User Defined Types to Hold string as table Starts --
CREATE OR REPLACE TYPE SPLITTED_TEXT AS OBJECT(SPLITTED_VALUE VARCHAR2(50));
CREATE OR REPLACE TYPE SPLITTED_TEXT_TABLE AS TABLE OF SPLITTED_TEXT;
-- User Defined Types to Hold string as table Ends--

-- Function to split delimiter string Starts --

CREATE OR REPLACE
FUNCTION OBF_SPLIT_DELIMITER_STRING_FN(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN SPLITTED_TEXT_TABLE PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
    --v_tempstring SPLITTED_TEXT_TABLE.SPLITTED_VALUE%type;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index    := INSTR(p_string, p_delimiter, v_start);

        IF v_index = 0
        THEN    
           -- v_tempstring := SUBSTR(p_string,v_start);
            PIPE ROW(new SPLITTED_TEXT(SUBSTR(p_string,v_start)));
            v_start    := v_length + 1;
        ELSE
            --v_tempstring := SUBSTR(p_string, v_start, v_index - v_start);
            PIPE ROW(new SPLITTED_TEXT(SUBSTR(p_string, v_start, v_index - v_start)));
            v_start    := v_index + 1;
        END IF;
    END LOOP;

    RETURN;
END OBF_SPLIT_DELIMITER_STRING_FN;

-- Function to split delimiter string Ends--

-- Usage of Function Starts --

select * from table(OBF_SPLIT_DELIMITER_STRING_FN('one#two#three#four','#'))

-- Usage of Function Ends--