create table a (B number(10));

begin

for i in 1..50 loop
  insert into a values(i);
end loop;
end;
/

declare
ss varchar2(32767);
CURSOR cur is
select B from a;
begin
for i in cur loop
  ss:= ss||i.B||' ';
end loop;
dbms_output.put_line(ss);
end;
/

------解决方案--------------------
這樣？
SQL code
WITH t AS(
SELECT 1 A,5 B,6 C FROM DUAL
UNION ALL
SELECT 4,6,5 FROM DUAL
UNION ALL 
SELECT 3,7,5 FROM DUAL
)
SELECT REPLACE(WM_CONCAT(B),',','')AS B FROM T;

output:
B
567

------解决方案--------------------
只要B字段的數據橫著顯示而已？還是必須生成多個字段？
如果是後者要複雜些的存儲，如果是前者的需求，直接wm_concat?
SQL code
WITH t AS(
SELECT 1 A,5 B,6 C FROM DUAL
UNION ALL
SELECT 4,6,5 FROM DUAL
UNION ALL 
SELECT 3,7,5 FROM DUAL
)
SELECT REPLACE(WM_CONCAT(B),',','')AS B FROM T;

output:
B
567

------解决方案--------------------
prompt
prompt Creating type STRCAT_TYPE
prompt =========================
prompt
create or replace type strcat_type as object
(
 cat_string varchar2(4000),
--对象初始化
 static function ODCIAggregateInitialize(cs_ctx In Out strcat_type)
   return number,
--聚合函数的迭代方法(这是最重要的方法)
 member function ODCIAggregateIterate(self  In Out strcat_type,
                                      value in varchar2) return number,
--当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
 member function ODCIAggregateMerge(self In Out strcat_type,
                                    ctx2 In Out strcat_type) return number,
--终止聚集函数的处理,返回聚集函数处理的结果
 member function ODCIAggregateTerminate(self        In Out strcat_type,
                                        returnValue Out varchar2,
                                        flags       in number)
   return number
)
/

prompt
prompt Creating type body STRCAT_TYPE
prompt ==============================
prompt
create or replace type body strcat_type is
 static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number
 is
 begin
     cs_ctx := strcat_type( null );
     return ODCIConst.Success;
 end;
 member function ODCIAggregateIterate(self IN OUT strcat_type,
                                     value IN varchar2 )
 return number
 is
 begin
       /*字符串已','分割 */
     self.cat_string := self.cat_string || ','|| value;
     return ODCIConst.Success;
 end;
 member function ODCIAggregateTerminate(self IN Out strcat_type,
                                       returnValue OUT varchar2,
                                       flags IN number)
 return number
 is
 begin
       /*去除空(is null)*/
     returnValue := ltrim(rtrim(self.cat_string,','),',');
     return ODCIConst.Success;
 end;
 member function ODCIAggregateMerge(self IN OUT strcat_type,
                                   ctx2 IN Out strcat_type)
 return number
 is
 begin
     self.cat_string := self.cat_string || ',' || ctx2.cat_string;
     return ODCIConst.Success;
 end;
end;
/

prompt
prompt Creating function FUNC_STRCAT
prompt =============================
prompt
CREATE OR REPLACE FUNCTION func_strcat(input varchar2) RETURN varchar2 -- 返回值
 PARALLEL_ENABLE
 AGGREGATE USING strcat_type;