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

Storing data as delimiter separated values is a bad practice and should be avoided. Normalize your data model and store values separately. Read [**this**](https://stackoverflow.com/a/3653574/7998591) to understand more — Kaushik Nayak, Jul 16 '19 at 10:18
Please post your required output as text. Many people can't - or won't try to - access screenshots in image dump sites. — APC, Jul 16 '19 at 10:38
Thank you Kaushik Nayak. However table is not my under control, business team manage the tablo. Therefore I can't change the source — gonyali, Jul 16 '19 at 10:55

score 1 · Answer 1 · answered Jul 16 '19 at 08:34

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

answered Jul 16 '19 at 08:34

Popeye

35,427
4
10
31

Thank you, but I have 9 columns in my orijinal table. Therefore I try to create a function with cursor. Sorry to forget to write it. I'm editing my post now – gonyali Jul 16 '19 at 08:40
By using the PL/SQL function, You will reduce the performance of the query. If you have 9 such columns then also you can write all columns in the above query. and yes, This is not a good practice of storing data like this. You must take action to improve the design of your DB. – Popeye Jul 16 '19 at 10:17
Unfortunately I can't change this table, because business unit managing the table. And I have tried to create the above function long time :) therefore I would like to learn how can I create this – gonyali Jul 16 '19 at 12:18
Again, If you will create functio the you will increase the contect switching and your query will be unable to perform well.. performance will be degraded. And about DB design, It's OK if it is not in your plate. :) – Popeye Jul 16 '19 at 13:01

score 0 · Answer 2 · answered Jul 16 '19 at 16:58

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 )

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

2 Answers2