Categories
11g sql xml

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

By Laurent Schneider

Oracle Certified Master

18 replies on “select from comma-separated list”

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

Thanks!

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

[…] Link to the original site Tags: Description Column, Nbsp, ORACLE BLOGS, Quot Quot […]

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;

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;

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)

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

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;
/

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 Reply

Your email address will not be published.