-
hi guys,
pl. find a solution for this :
assuming a simple table, that an emp table has got 3 cols like empno,ename,esal. and contains 10 rows.
if we give select empno,ename,esal from emp;
it will list you all ten rows corresponding to empno.
but i want to see only all empno's in horizontal manner at a time.
ie., not like :
empno
=====
1001
1002
1003
..
..
1010
but i want see them like
empno
=====
1001, 1002, 1003, ....1010
at sql prompt
thanks
-
Hi,
Send me your email ID at oracledba8@orasearch.net
I'll send you a script that can solve your problem.
Thanks. Qaisar
-
create this function:
create or replace function doit(fieldin varchar2, tablein varchar2) return varchar2 is
wholestring varchar2(4000) := '';
nextstring varchar2(4000) := '';
TYPE t_cursor is REF CURSOR;
v_cursor t_cursor;
begin
open v_cursor for 'select '||fieldin||' from '||tablein;
loop
fetch v_cursor into nextstring;
exit when v_cursor%notfound or ((length(wholestring)+length(nextstring)) > 4000);
if wholestring is not null then
wholestring := wholestring||', '||nextstring;
else
wholestring := nextstring;
end if;
end loop;
return wholestring;
end;
/
pass in two strings: the first is a string representing an expression which returns a string, such as a char or varchar2 field name, or to_char(numberfield). The second parameter to be passed in is a string containing the table name (including schema name if appropriate).
For example:
select doit('to_char(id)', 'emp') summary from dual;
select doit('name', 'emp') summary from dual;
OR
set serveroutput on
set heading off
set pagesize 10000
set linesize 122
exec dbms_output.put_line(doit('name', 'emp'));
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|