How can I create pipelined function with cursor, and return table?

Question

I have a table like (Here 9 columns with ';'. This is sample table):

create table mytable as (
select
  1 ID,
  'T1;T2;T3' column_1,
  'B1;B5;B10;B13' column_2
from dual
union all
select
  2 ID,
  'T7;T8;T9;T10,T11',
  'B2;B3;B5'
from dual
)

I need target table like:

ID  column_1    column_2
1      T1        B1
1      T1        B5
1      T1        B10
1      T1        B13
1      T2        B1
1      T2        B5
1      T2        B10
1      T2        B13
1      T3        B1
1      T3        B5
1      T3        B10
1      T3        B13
2      T7        B2
2      T7        B3
2      T7        B5
2      T8        B2
2      T8        B3
2      T8        B5
2      T9        B2
2      T9        B3
2      T9        B5
2      T10       B2
2      T10       B3
2      T10       B5
2      T11       B2
2      T11       B3
2      T11       B5

I found the below link: pipelined function with cursor parameter oracle but I cannot create function regularly. I create function for only one column but can't loop, and I can't call a table. Here is my function:

create or replace function fun_pipelined(i_str in varchar2)
  RETURN sys.odcivarchar2list PIPELINED
IS
  v_arr     dbms_sql.varchar2_table;
  v_i       long;
  v_cnt     number;
  i         number;
begin
  v_arr := pl.split(nvl(i_str,' ,'),',');
  v_cnt := regexp_count(nvl(i_str,','), ',') + 1;
  i := 1;
  loop
    exit when i > v_cnt;
    v_i := trim(v_arr(i));
    pipe row (v_i);
    i := i+1;
  end loop;
end;

Could you please give me some advice? Thank you

Answer 1

You can try the following query:

WITH DATAA AS (
    SELECT DISTINCT
        ID,
        REGEXP_SUBSTR(COLUMN_1, '[^;]+', 1, LEVEL) COLUMN_1,
        REGEXP_SUBSTR(COLUMN_2, '[^;]+', 1, LEVEL) COLUMN_2
    FROM
        MYTABLE
    CONNECT BY REGEXP_SUBSTR(COLUMN_1, '[^;]+', 1, LEVEL) IS NOT NULL
               OR REGEXP_SUBSTR(COLUMN_2, '[^;]+', 1, LEVEL) IS NOT NULL
)
SELECT
    ID,
    COLUMN_1,
    COLUMN_2
FROM
    (
        SELECT DISTINCT
            D1.ID,
            D1.COLUMN_1,
            D2.COLUMN_2
        FROM
            DATAA D1
            JOIN DATAA D2 ON ( D1.ID = D2.ID )
    )
WHERE
    ( COLUMN_1 IS NOT NULL
      AND COLUMN_2 IS NOT NULL )
ORDER BY
    ID,
    COLUMN_1;

db<>fiddle demo

Cheers

Answer 2

I prefer common table expressions to Oracle's connect by. Here is your result using a CTE.

WITH
    mytable AS
        (SELECT 1 id, 'T1;T2;T3' column_1, 'B1;B5;B10;B13' column_2 FROM DUAL
         UNION ALL
         SELECT 2 id, 'T7;T8;T9;T10;T11', 'B2;B3;B5' FROM DUAL),
    mytable2 AS( SELECT id, column_1 || ';' AS column_1, column_2 || ';' AS column_2 FROM mytable ),
    splitset1 ( id
              , column_1
              , column_2
              , REMAINDER ) AS
        (SELECT id
              , SUBSTR( column_1
                      , 1
                      , INSTR( column_1, ';' ) - 1 )              AS column1
              , column_2
              , SUBSTR( column_1, INSTR( column_1, ';' ) + 1 )    AS REMAINDER
           FROM mytable2
         UNION ALL
         SELECT id
              , SUBSTR( REMAINDER
                      , 1
                      , INSTR( REMAINDER, ';' ) - 1 )
              , column_2
              , SUBSTR( REMAINDER, INSTR( REMAINDER, ';' ) + 1 )
           FROM splitset1
          WHERE REMAINDER IS NOT NULL),
    splitset2 ( id
              , column_1
              , column_2
              , REMAINDER ) AS
        (SELECT id
              , column_1
              , SUBSTR( column_2
                      , 1
                      , INSTR( column_2, ';' ) - 1 )              AS column2
              , SUBSTR( column_2, INSTR( column_2, ';' ) + 1 )    AS REMAINDER
           FROM splitset1
         UNION ALL
         SELECT id
              , column_1
              , SUBSTR( REMAINDER
                      , 1
                      , INSTR( REMAINDER, ';' ) - 1 )
              , SUBSTR( REMAINDER, INSTR( REMAINDER, ';' ) + 1 )
           FROM splitset2
          WHERE REMAINDER IS NOT NULL)
  SELECT id
       , column_1
       , column_2
    FROM splitset2
ORDER BY id
       , CAST( SUBSTR( column_1, 2 ) AS NUMBER )
       , CAST( SUBSTR( column_2, 2 ) AS NUMBER )

If you have Oracle 12, you can make your SQL very readable using SQL functions at the expense of some overhead:

WITH
    FUNCTION after( p_value IN VARCHAR2, p_separator IN VARCHAR2 DEFAULT ';' )
        RETURN VARCHAR2 AS
        l_pos   INTEGER;
    BEGIN
        l_pos   := INSTR( p_value, p_separator );
        RETURN CASE WHEN l_pos > 0 THEN SUBSTR( p_value, l_pos + 1 ) ELSE NULL END;
    END after;

    FUNCTION before( p_value IN VARCHAR2, p_separator IN VARCHAR2 DEFAULT ';' )
        RETURN VARCHAR2 AS
        l_pos   INTEGER;
    BEGIN
        l_pos   := INSTR( p_value, p_separator );
        RETURN CASE
                   WHEN l_pos > 0
                   THEN
                       SUBSTR( p_value
                             , 1
                             , l_pos - 1 )
                   ELSE
                       p_value
               END;
    END before;

    mytable AS
        (SELECT 1 id, 'T1;T2;T3' column_1, 'B1;B5;B10;B13' column_2 FROM DUAL
         UNION ALL
         SELECT 2 id, 'T7;T8;T9;T10;T11', 'B2;B3;B5' FROM DUAL),
    mytable2 AS( SELECT id, column_1 || ';' AS column_1, column_2 || ';' AS column_2 FROM mytable ),
    splitset1 ( id
              , column_1
              , column_2
              , REMAINDER ) AS
        (SELECT id
              , before( column_1 )     AS column1
              , column_2
              , after( column_1 )      AS REMAINDER
           FROM mytable2
         UNION ALL
         SELECT id
              , before( REMAINDER )
              , column_2
              , after( REMAINDER )
           FROM splitset1
          WHERE REMAINDER IS NOT NULL),
    splitset2 ( id
              , column_1
              , column_2
              , REMAINDER ) AS
        (SELECT id
              , column_1
              , before( column_2 )     AS column2
              , after( column_2 )      AS REMAINDER
           FROM splitset1
         UNION ALL
         SELECT id
              , column_1
              , before( REMAINDER )
              , after( REMAINDER )
           FROM splitset2
          WHERE REMAINDER IS NOT NULL)
  SELECT id
       , column_1
       , column_2
    FROM splitset2
ORDER BY id
       , CAST( SUBSTR( column_1, 2 ) AS NUMBER )
       , CAST( SUBSTR( column_2, 2 ) AS NUMBER )