Hii All,
I've the following function returning a select query,but instead I'd like to return the rows retrived from the query. I could make use of pipelined function if I knew the number of columns that are going to be returned but here In my case as the query returns columns dynamically I'm not able to make use of pipelined Function. How can I acheive this?Please help me.
Create or replace Function r_return_col
(
p_table_name in varchar2,
p_column_name in varchar2
)return varchar2
is
l_count number;
l_clob clob;
l_count_str varchar2(32000);
l_column_value varchar2(32000);
l_str varchar2(32000);--clob;
l_ret_str clob;
begin
l_count_str := ' select count('||p_column_name||') ' ;
l_count_str := l_count_str||' from '||p_table_name ;
execute immediate l_count_str into l_count;
dbms_output.put_line('Count is : '||l_count);
for i in 1..l_count
loop
l_str := 'select '||p_column_name||' from ' ;
l_str := l_str||' ( ';
l_str := l_str||' select row_number() over(order by '||p_column_name||') rw,'||p_column_name||' from '||p_table_name;
l_str := l_str||' ) where rw = '||i ;
execute immediate l_str into l_column_value;
l_column_value := q'[']'||l_column_value||q'[']' ;
l_ret_str := l_ret_str||','||l_column_value;
end loop;
return 'select '||ltrim(l_ret_str,',')||' from dual';
end;
/
show err;
Regards
Raghu