Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

Transforming from list to a set of rows

Posted by Riyaj Shamsudeen on January 11, 2007

A developer asked me how to transform a column with a list of values to a set of rows ?
Column value is of this format:
1, ‘100,101,102,103,104,105’
Requirement was to convert above value to the format below:
1, 100
1, 101
1, 102

Solution

:

It is easy to do this using a pipelined function. Rows returned from a function can be used as a row source, as if it is a table row source. Metalink document 136909.1 describes this pipelined functions excellently.

Here is the test case, for the solution. Some part of code is borrowed from Tom Kyte’s original asktom posting.

– Dropping, recreate table tlist and populate with two rows.
drop table tlist;
create table tlist (c1 number, c2 varchar2(30) );
insert into tlist values (1,’100,101,102,103,104,105′);
insert into tlist values (2,’200,201,202,203,204,205′);
commit;

– Pipelined functions returns collections. Declaring a type TestListObj.
— TestList is an array of TestListObj
drop type TestList;
drop type TestListObj;
create or replace type TestListObj as object
(c1 number, c2 number);
create or replace type TestList as table of TestListObj;

— Creating a pipelined function.
REM This function :
REM for each row from tlist table
REM Read column values c1 and c2.
REM c2 has list of values separated by comma
REM For each value from the list
REM Construct TestListObj and store that as a member of TestList table object
REM Return TestList

create or replace function in_list_Test
return
TestList
as

l_string
long;
l_data
TestList := TestList();
n number;
begin
for l_csr in (select c1,c2 from tlist )
loop
l_string := l_csr.c2 || ‘,';
loop
exit when l_string is null;
n := instr( l_string, ‘,’ );
l_data.extend;
l_data(l_data.count) := TestListObj( l_csr.c1, ltrim( rtrim( substr( l_string, 1, n-1 ) ) ) );
l_string := substr( l_string, n+1 );
end loop;
end loop;
return l_data;
end;

pause
show errors
select * from table (cast (in_list_Test as TestList))
/

C1 C2
1 100
1 101
1 102
….
Hope, you can make use of this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 200 other followers

%d bloggers like this: