Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to return rows from the following function

795356Oct 25 2010 — edited Oct 25 2010
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 22 2010
Added on Oct 25 2010
22 comments
1,102 views