Jun 28 2008, 04:27 AM
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;
but this procedure give me error....(ORA-01422: exact fetch returns more than requested number of rows)
Please Help me
Thanks in Advance
Jun 28 2008, 11:04 PM
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.
Jun 30 2008, 08:58 AM
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:
for line in ( select ids from tmpids order by ids) loop
if ind=1 then list1:=list1||','; end if; ind:=1;
Jun 30 2008, 02:21 PM
"i want to give result like this in one variable...(1,2,3,4,5,6,7,8,9,10)......"
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 . . . . .