Pipelined output

S

Short and simple demo for using pipelined functions. Perfect way how to get content from PL/SQL procedure accessible in SQL as a table.

CREATE OR REPLACE FUNCTION string_split (
    in_src      VARCHAR2,
    in_sep      VARCHAR2
)
RETURN str_4k PIPELINED
AS
    i           PLS_INTEGER;
    items       VARCHAR2(32767) := in_src;  -- TRIM(BOTH in_sep FROM in_src);
BEGIN
    LOOP
        i := INSTR(items, in_sep);
        IF i > 0 THEN
            PIPE ROW(SUBSTR(items, 1, i - 1));
            items := SUBSTR(items, i + LENGTH(in_sep));
        ELSE
            PIPE ROW(items);
            EXIT;
        END IF;
    END LOOP;
    RETURN;
END;
/

Data type is needed but it can be stored in package specification.

CREATE TYPE string_4k AS TABLE OF VARCHAR2(4000);
/

And finally SQL:

SELECT
    ROWNUM          AS position,
    COLUMN_VALUE    AS value
FROM TABLE (
    string_split('A,B,C,,,D,E', ',')
);