我在网上下了个wmsys.wm_concat的定义执行成功了,但是创建的视图依然报错说这个函数是无效的标识符,函数及视图代码下:
--一:创建type头create or replace type string_sum_obj as object ( 
--聚合函数的实质就是一个对象 
     sum_string varchar2(4000), 
     static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number, 
     --对象初始化 
     member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number, 
     --聚合函数的迭代方法(这是最重要的方法) 
     member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number, 
     --当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合 
      
     member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number 
     --终止聚集函数的处理,返回聚集函数处理的结果. 
);
--创建type具体
create or replace type body string_sum_obj is 
     static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number is 
     begin 
         v_self := string_sum_obj(null); 
         return ODCICONST.Success; 
     end; 
     member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number is 
     begin 
          /* 连接,解决逗号分隔第一个字母是逗号的问题 */    
           if not self.sum_string is null then
          self.sum_string := self.sum_string ||','|| value; 
          else
          self.sum_string := self.sum_string || value; 
          end if;
          return ODCICONST.Success; 
          /* 最大值 */ 
          if self.sum_string<value then 
              self.sum_string:=value; 
          end if; 
          /* 最小值 */ 
          if self.sum_string>value then 
       self.sum_string:=value;           
          end if; 
           
          return ODCICONST.Success; 
     end; 
     member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number is 
     begin 
          /* 连接 */    
          self.sum_string := self.sum_string || v_next.sum_string; 
          return ODCICONST.Success; 
          /* 最大值 */ 
          if self.sum_string<v_next.sum_string then 
              self.sum_string:=v_next.sum_string; 
          end if;          /* 最小值 */ 
          if self.sum_string>v_next.sum_string then 
              self.sum_string:=v_next.sum_string;           
          end if; 
           
          return ODCICONST.Success; 
     end; 
     member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number is 
     begin 
          return_value:= self.sum_string; 
          return ODCICONST.Success; 
     end; 
end;
--创建函数
create or replace function wm_concat(value Varchar2) return Varchar2 
     parallel_enable aggregate using string_sum_obj;--二:先创建这个类型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
);--缺少类型体内容:然后创建这个函数CREATE OR REPLACE FUNCTION strcat(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING strcat_type;我创建的视图如下:
CREATE OR REPLACE VIEW ZC_BI_IMPORT_JB AS
SELECT "ZC_BI_NO","CO_CODE","PROJECT_CODE_ZHU","PROJECT_CODE","PROJECT_NAME","ZC_CATALOGUE_CODE","ZC_CONSOLE","ZC_MODEL","ORIGIN_CODE",
       "ZC_CONTROL_SUM","ZC_CONTROL_NUM","ZC_BI_SUM","ZC_BI_USED_SUM","ND","STOCK_NO"
  FROM (SELECT PROJECT_CODE  ZC_BI_NO,
               CO_CODE, --单位代码
               project_code_zhu,--主项目
               PROJECT_CODE, --明细预算项目编号
               wmsys.wm_concat(DISTINCT(PROJECT_NAME)) PROJECT_NAME, --明细预算项目名称
               D_ATTR1 ZC_CATALOGUE_CODE, --采购目录编号
               '1' ZC_CONSOLE, --采购平台
               /*STOCK_TYPE ZC_MODEL, --采购模式*/
               '01' ZC_MODEL, --采购模式
               ORIGIN_CODE, --资金来源
               max(stock_price*10000) ZC_CONTROL_SUM, --单价
               SUM(STOCK_NUMBER) ZC_CONTROL_NUM, --数量
               SUM(AM_MONEY) ZC_BI_SUM, --总价
               sum(ZC_BI_USED_SUM) ZC_BI_USED_SUM,--明细项目已用金额
               ND, --年度
               '' stock_no
          FROM (SELECT A.CO_CODE,
                       a.project_code project_code_zhu,--主项目代码
                       a.project_code||'-'||A.D_ATTR1 PROJECT_CODE,--明细项目代码
                       D.PD_PROJECT_NAME PROJECT_NAME,
                       D_ATTR1,
                       A.BI_MONEY        AM_MONEY,
                       a.am_money ZC_BI_USED_SUM,
                       C.STOCK_PRICE,
                       C.STOCK_TYPE,
                       A.ORIGIN_CODE,
                       C.STOCK_NUMBER,
                       A.ND
                  FROM BI_BALANCE A,
                       (select pd_project_code,max(pd_project_name) pd_project_name from GK_XMZC_MX_V
                               /*WHERE nd='2011' and pd_version_id ='N2011'*/ GROUP BY PD_PROJECT_CODE) D,
                       (SELECT B.TARGET_BALANCE_ID,
                        STOCK_TYPE,
                               MAX(NVL(B.STOCK_PRICE, 0)) STOCK_PRICE,
                               SUM(NVL(B.STOCK_NUMBER, 0)) STOCK_NUMBER,
                               ND
                          FROM BI_TRACK B
                         WHERE B.IS_STOCK = '1'
                           and b.stock_type='0'
                           AND B.MANAGE_CODE = '02'
                         GROUP BY B.TARGET_BALANCE_ID,                               STOCK_TYPE,
                                  ND) C,
                        zc_b_catalogue zc
                 WHERE C.TARGET_BALANCE_ID = A.BI_BALANCE_ID
                   AND A.ND = C.ND
                   AND A.PROJECT_DETAIL_CODE NOT IN ('2011-1242011-001057','2011-1242011-001052')--特殊处理维护单号Z158
                   AND A.PROJECT_DETAIL_CODE = D.PD_PROJECT_CODE(+)
                   and a.d_attr1=zc.zc_catalogue_code and a.nd=zc.zc_year
                   and zc.zc_default_console<>'1'
                   )
         GROUP BY CO_CODE,
                  project_code_zhu,
                  PROJECT_CODE,
                  D_ATTR1,
                 /* STOCK_TYPE,*/
                  ORIGIN_CODE,
                  ND
  union all
  SELECT PROJECT_CODE  ZC_BI_NO,
               CO_CODE, --单位代码
               project_code_zhu,--主项目
               PROJECT_CODE, --明细预算项目编号
               wmsys.wm_concat(DISTINCT(PROJECT_NAME)) PROJECT_NAME, --明细预算项目名称
               D_ATTR1 ZC_CATALOGUE_CODE, --采购目录编号
               '1' ZC_CONSOLE, --采购平台
               /*STOCK_TYPE ZC_MODEL, --采购模式*/
               '01' ZC_MODEL, --采购模式
               ORIGIN_CODE, --资金来源
               max(stock_price*10000) ZC_CONTROL_SUM, --单价
               SUM(STOCK_NUMBER) ZC_CONTROL_NUM, --数量
               SUM(AM_MONEY) ZC_BI_SUM, --总价
               sum(ZC_BI_USED_SUM) ZC_BI_USED_SUM,--明细项目已用金额
               ND, --年度
               stock_no
          FROM (SELECT A.CO_CODE,
                       a.project_code project_code_zhu,--主项目代码
                       a.project_detail_code PROJECT_CODE,--明细项目代码
                       D.PD_PROJECT_NAME PROJECT_NAME,
                       D_ATTR1,
                       A.BI_MONEY        AM_MONEY,
                       a.am_money ZC_BI_USED_SUM,
                       C.STOCK_PRICE,
                       C.STOCK_TYPE,
                       A.ORIGIN_CODE,
                       C.STOCK_NUMBER,
                       A.ND,
                       c.stock_no
                  FROM BI_BALANCE A,
                       (select pd_project_code,max(pd_project_name) pd_project_name from GK_XMZC_MX_V
                               /*WHERE nd='2011' and pd_version_id ='N2011'*/ GROUP BY PD_PROJECT_CODE) D,
                       (SELECT B.TARGET_BALANCE_ID,b.stock_no stock_no,
                        STOCK_TYPE,
                               MAX(NVL(B.STOCK_PRICE, 0)) STOCK_PRICE,
                               SUM(NVL(B.STOCK_NUMBER, 0)) STOCK_NUMBER,
                               ND
                          FROM BI_TRACK B
                         WHERE B.IS_STOCK = '1'
                           and b.stock_type='0'
                           AND B.MANAGE_CODE = '02'
                         GROUP BY B.TARGET_BALANCE_ID,
                               b.stock_no,
                               STOCK_TYPE,
                                  ND) C,
                        zc_b_catalogue zc
                 WHERE C.TARGET_BALANCE_ID = A.BI_BALANCE_ID
                   AND A.ND = C.ND
                   AND A.PROJECT_DETAIL_CODE NOT IN ('2011-1242011-001057','2011-1242011-001052')--特殊处理维护单号Z158
                   AND A.PROJECT_DETAIL_CODE = D.PD_PROJECT_CODE(+)
                   and a.d_attr1=zc.zc_catalogue_code and a.nd=zc.zc_year
                   and zc.zc_default_console='1'
                   )
         GROUP BY CO_CODE,
                  project_code_zhu,
                  PROJECT_CODE,
                  D_ATTR1,
                 /* STOCK_TYPE,*/
                  ORIGIN_CODE,
                  ND,
                  stock_no
                  )
 WHERE ZC_BI_SUM > 0
 ORDER BY CO_CODE, PROJECT_CODE, ZC_BI_SUM--select * from zc_p_bi_detail