Oracle database internals by Riyaj

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

Archive for the ‘SQL new features’ Category

New features in SQL

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



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′);

— 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

TestList := TestList();
n number;
for l_csr in (select c1,c2 from tlist )
l_string := l_csr.c2 || ‘,’;
exit when l_string is null;
n := instr( l_string, ‘,’ );
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;

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.

Posted in SQL new features | Leave a Comment »