Convert list data to Table

Some datas in PPM systems could store as a list with a delimiter. For example in PPM, project managers of a projects stores as a list with “;” delimiter. Although some reporting systems (like SAP BW) couldn’t understand this. To solve this problem, you could create a view which reporting system use it for ETL (Extraction, Transformation, and Loading) with below function:

The function is created in Oracle:

create or replace function str2table( p_string in varchar2, delimiter in varchar2 )
return myArray
as
l_data myArray := myArray();
l_string long := p_string;
l_n number;
begin
while (l_string is not null)
loop
l_n := instr( l_string, delimiter );
if ( l_n = 0 )
then
l_n := length(l_string)+1;
end if;
l_data.extend;
l_data(l_data.count) := substr( l_string, 1,l_n-1 );
l_string := substr( l_string, l_n+length(delimiter) );
end loop;
return l_data;
end;

Usage of this function in a view SQL  like this:

SELECT

mng_new_id.column_value

FROM

TABLE(ppmnartest_usr.STR2TABLE(prjfg.PRJ_PROJECT_MANAGER_USER_ID,’#@#’)) mng_new_id

Leave a Reply

Skip to toolbar