select from comma-separated list

This is asked over and over in the forums, but why not proposing an 11g solution here 😉

create table t(description varchar2(12) primary key,
numbers varchar2(4000));
insert into t(description, numbers) values ('PRIME','2,3,5,7');
insert into t(description, numbers) values ('ODD','1,3,5,7,9');
commit;

DESCRIPTION NUMBERS
PRIME 2,3,5,7
ODD 1,3,5,7,9

Now I want to unpivot numbers in rows


select description,(column_value).getnumberval()
from t,xmltable(numbers)

DESCRIPTION (COLUMN_VALUE).GETNUMBERVAL()
PRIME 2
PRIME 3
PRIME 5
PRIME 7
ODD 1
ODD 3
ODD 5
ODD 7
ODD 9

It is that simple 🙂

Works also with strings :

select (column_value).getstringval()
from xmltable('"a","b","c"');

(COLUMN_VALUE).GETSTRINGVAL()
a
b
c

Published by Laurent Schneider

Oracle Certified Master

Join the Conversation

18 Comments

  1. This is an elegant solution in 11g, but how would you do this in Oracle 9i?

    Thanks!

  2. I would create a plsql function


    CREATE OR REPLACE TYPE lsc_number AS OBJECT (
    x NUMBER
    );
    /

    CREATE OR REPLACE TYPE lsc_number_collection AS TABLE OF lsc_number;
    /

    CREATE OR REPLACE FUNCTION lsc_f (str VARCHAR2, delim VARCHAR2 := ',')
    RETURN lsc_number_collection PIPELINED
    IS
    tmpstr VARCHAR2 (4000) := str || delim;
    BEGIN
    WHILE tmpstr IS NOT NULL
    LOOP
    PIPE ROW (lsc_number (SUBSTR (tmpstr, 1, INSTR (tmpstr, delim) - 1)));
    tmpstr := SUBSTR (tmpstr, INSTR (tmpstr, delim) + 1);
    END LOOP;

    RETURN;
    END;
    /

    SQL> select description, x from t, TABLE (lsc_f (numbers));

    DESCRIPTION X
    ------------ ----------
    PRIME 2
    PRIME 3
    PRIME 5
    PRIME 7
    ODD 1
    ODD 3
    ODD 5
    ODD 7
    ODD 9

    It should perform better 🙂

    xmltable is a 10gR2 function, but only in 11g it accepts dynamic programming

  3. Pingback: select from column-separated list | Oracle
  4. sending departments as comma sperated string (10,20) in input parameter (ip_string), could not compile following procedure

    create or replace procedure extract_string(ip_string in varchar2,op_emp out sys_refcursor)
    as
    begin
    open op_emp for
    select E.ENAME,E.EMPNO
    from emp e
    where e.deptno in(
    select (column_value).getstringval()
    from xmltable(ip_string));
    end extract_string;

  5. are you using 11g?

    In 10g you should use :
    create or replace procedure extract_string(ip_string in varchar2,op_emp out sys_refcursor)
    as
    begin
    open op_emp for
    'select E.ENAME,E.EMPNO from emp e
    where e.deptno in( select (column_value).getstringval()
    from xmltable('''||ip_string
    ||'''))';
    end extract_string;

  6. but indeed the xmltable(plsqlvariable) does not seem to work in 11g neither. Open a bug by metalink.

    As a workaround you can use xmltable((select ip_string from dual)) instead of xmltable(ip_string)

  7. yes i am using 11g. thanks but the wrokaround also giving error in 11g at compilation

    ERROR line 9, col 15, ending_line 9, ending_col 20, Found ‘select’, Expecting: XMLNAMESPACES -or- string

  8. which 11g are you using?

    In 11.2.0.1.0

    create or replace procedure extract_string(ip_string in varchar2,op_emp out sys_refcursor)
    as
    begin
    open op_emp for
    select E.ENAME,E.EMPNO from emp e
    where e.deptno in( select (column_value).getstringval() from xmltable((select ip_string x from dual)));
    end extract_string;

    Procedure created.

    But I would prefer a no-xml solution like


    create or replace procedure extract_string(ip_string in varchar2,op_emp out sys_refcursor)
    as
    begin
    if regexp_like(ip_string,’^\d+(,\d+)*$’) then
    open op_emp for ‘select E.ENAME,E.EMPNO from emp e where e.deptno in (‘||ip_string||’)’;
    end if;
    end extract_string;
    /

  9. true, but the cost of having dynamic xquery evaluation is tremendous anyway!

    A better alternative would be to use collections instead of strings


    create or replace type deptnolist as table of number
    /

    create or replace procedure extract_string(ip_string in deptnolist,op_emp out sys_refcursor)
    as
    begin
    open op_emp for select E.ENAME,E.EMPNO from lsc_emp e where e.deptno member of ip_string;
    end extract_string;
    /

    var r refcursor;
    set autop on;
    exec extract_string(deptnolist(10,20),:r);

Leave a comment

Your email address will not be published.