Hi All ,

i have a table with one column .... in this table there are 10 rows.....i want to give result like this in one variable...(1,2,3,4,5,6,7,8,9,10)......my point is how to write select query..when this is give me this type of result....i write a procedure

CREATE OR REPLACE Procedure Getlist() as
list1 VARCHAR2(8000) default '';
select COALESCE ( nullif(list1,'') || ',','') || ids Into list1 from tmpIDS;
select NVL(list1,'') into list1 from dual;
END GETLIST;
/

but this procedure give me error....(ORA-01422: exact fetch returns more than requested number of rows)

Please Help me

Thanks in Advance

Why would you do this in PL/SQL anyway? What you are looking for is pivot, row to column, etc. Google it and pick one of the examples that best suits your needs.

Hi,
u can't get result from table into (one) variable
(too many row is not exception from procedure, but from you select)

U may try samething of a:

declare
list1 varchar2(200):='';
ind number:=0;
begin
for line in ( select ids from tmpids order by ids) loop
if ind=1 then list1:=list1||','; end if; ind:=1;
list1:=list1||line.ids;
end loop;
end;

"i want to give result like this in one variable...(1,2,3,4,5,6,7,8,9,10)......"

Why?

Is this what you want, displaying multiple columns on one row?

http://www.dba-oracle.com/t_sql_pivoting_r...ral_columns.htm

If you want to put the result into a single variable, you could concatenate it on output:

select "("||col1||','||col2||','col3||")" bigfield . . . . .