Do you have any comments about this implementation (10.2+)? Do you
know of better ways to do this? Does this one have any major issues
in your opinion?
Thanks, Joel
(PS -Sorry about the extra PM on this one David. Tried to reply to
the old message but hit wrong reply button accidentally.)
create or replace type split_type is table of varchar2 (4000);
create or replace function split_string (p_string in varchar2)
return split_type
as
l_data split_type := split_type ();
cursor c_list
is
select trim (both ' ' from regexp_substr (str, '[^,]+', 1,
level)) as single_element
from (select p_string str from dual)
connect by instr (str, ',', 1, level - 1) > 0;
begin
for list_rec in c_list
loop
l_data.extend;
l_data (l_data.count) := list_rec.single_element;
end loop;
return l_data;
end;
create table t (id number, value varchar2(255));
insert into t values (1,'one,two,three');
insert into t values (2,'four, five, six');
insert into t values (3,'seven,eight, nine ');
commit;
select * from t;
select t1.id, t2.column_value num
from (select id, get_test_table (value) v from t) t1 natural join
table (t1.v) t2;
drop table t purge;
>On Dec 13, 9:03 am, Joel <[email protected]> wrote:
>
>> There are new functions pivot, unpivot, and listagg. Any idea how to
>> unlistagg?
>
>Not without parsing the output with, say, instr(). The listagg
>function is intended to generate report output, imo, not to generate
>data for loading elsewhere.
>
>David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
