This tutorial shows how to extract delimited values from a string like this
C --------------- 'v1','v2','v3'
and return them one value per row, like this.
POSITION VAL
---------- ----------
1 v1
2 v2
3 v3
The following dynamic PL/SQL¹ method works when the comma separated values are quoted character strings. Before proceeding though, let's look at our test data.
select * from t2 ;
KEY C NOTE --- --------------- ------------------------- r01 'v1' One value r02 'v1','' Two values, one empty r03 'v1','','' Three values, two empty r04 '','v2','' Three values, two empty r05 '','','v3' Three values, two empty r06 'v1','v2' Two values r07 'v1','v2','' Three values, one empty r08 'v1','v2','v3' Three values, none empty r09 '' One empty value r10 '','' Two values, two empty r11 '','','' Three values, all empty r12 (null) NULL value
The first step is to create a function that converts a delimited string into a VARRAY of values.
create function csv_to_varray( p_string in varchar2 )
return
varchar2_varray_type -- this type defined in Setup topic for this section
is
v_table varchar2_varray_type ;
begin
execute immediate
'begin :v_table := varchar2_varray_type( ' || p_string || ' ); end;'
using in out v_table
;
return( v_table );
end;
/
The varray is then easily converted to a set of rows using a TABLE( ) collection expression.
set null "(null)" set pagesize 45 break on key skip 1 duplicates select key, vt.column_value as val from t2 , table( csv_to_varray(t2.c) ) (+) vt ;
KEY VAL --- ---------- r01 v1 r02 v1 r02 (null) r03 v1 r03 (null) r03 (null) r04 (null) r04 v2 r04 (null) r05 (null) r05 (null) r05 v3 r06 v1 r06 v2 r07 v1 r07 v2 r07 (null) r08 v1 r08 v2 r08 v3 r09 (null) r10 (null) r10 (null) r11 (null) r11 (null) r11 (null) r12 (null)
Numeric values
If the comma separated values are numbers like these
KEY C NOTE --- ---------- ------------------------- r01 1 One value r02 1,2 Two values r03 1,2,3 Three values r04 (null) NULL value
then the function must be modified to return a different varray type.
create function csv_num_to_varray( p_string in varchar2 )
return
integer_varray_type -- this type defined in Setup topic for this section
is
v_table integer_varray_type ;
begin
execute immediate
'begin :v_table := integer_varray_type( ' || p_string || ' ); end;'
using in out v_table
;
return( v_table );
end;
/
As before, the varray is then easily converted to a set of rows using a TABLE collection expression.
select key, vt.column_value as numeric_val from t3 , table( csv_num_to_varray(t3.c) ) (+) vt ;
KEY NUMERIC_VAL --- ----------- r01 1 r02 1 r02 2 r03 1 r03 2 r03 3 r04 (null)
Gotchas
Note that both methods will produce errors if the delimited strings contain missing values, as in the strings " 'a',,'c' " and "1,,3".
select column_value as val
from table( csv_num_to_varray( '1,,3' ) ) vt ;
from table( csv_num_to_varray( '1,,3' ) ) vt
*
ERROR at line 2:
ORA-06550: line 1, column 41:
PLS-00103: Encountered the symbol "," when expecting one of the following:
...
Warnings
- Dynamic SQL and Dynamic PL/SQL techniques can make your application vulnerable to SQL injection attacks. Use them with care or avoid their use altogether in sensitive applications.