oracle 9i wm_concat 自建 及一些错误记载

create or replace type tmp 
authid current_user as object
(
  tmp_strings varchar2(32767),
  static function odciaggregateinitialize(p in out tmp) return number,
  member function odciaggregateiterate(self in out tmp,p in varchar2) return number,
  member function odciaggregateterminate(self in tmp,returnvalue out varchar2,flags in number)return number,
  member function odciaggregatemerge(self in out tmp,p in tmp) return number
);
/

create or replace type body tmp
is
  static function odciaggregateinitialize(p in out tmp) return number 
  is
  begin
    p := tmp(null) ;
    return odciconst.success;
  end;
  member function odciaggregateiterate(self in out tmp,p in varchar2) return number
  is
  begin
    if(tmp_strings is not null) then 
      tmp_strings := tmp_strings || ',' || p;
    else
      tmp_strings := p;
    end if;
    return odciconst.success;
  end;
  member function odciaggregateterminate(self in tmp,returnvalue out varchar2,flags in number)return number
  is
  begin
    returnvalue := tmp_strings ;
    return odciconst.success;
  end;
  member function odciaggregatemerge(self in out tmp,p in  tmp) return number
  is
  begin
    if(p.tmp_strings is not null) then 
      self.tmp_strings := self.tmp_strings || ',' || p.tmp_strings ;
    end if;
    return odciconst.success;
  end;
end;
/

create or replace function wm_concat(p varchar2) return varchar2 aggregate using tmp ;
/

 

但是

select run_id,
       t1.form_id,
       wm_concat(decode(t1.title, '工程编号', to_char(t2.item_data), null)) as buckNo,
       wm_concat(decode(t1.title, '工程名称', to_char(t2.item_data), null)) as gcName,
       wm_concat(decode(t1.title, '建设单位', to_char(t2.item_data), null)) as jsdw,
       wm_concat(decode(t1.title, '总投资', to_char(t2.item_data), null)) as bctze
  from FLOW_FORM_ITEM t1, FLOW_RUN_DATA t2, flow_type t3
 where t1.item_id = t2.item_id
   and t1.form_id = t3.form_seq_id
   and t3.SEQ_ID = 1622
 group by run_id, t1.form_id

会报错

 

 

还有9i

 

select SEQ_ID,
       USER_ID,
       USER_NAME,
       USER_PRIV_OTHER,
       DEPT_ID,
       USER_PRIV,
       DEPT_ID_OTHER
  from PERSON
 where NOT_LOGIN <> '1'
   AND instr(',' || USER_PRIV_OTHER || ',', ',845,') > 0
 order by USER_NO DESC, SEQ_ID

 

估计是字符串跟大字段连接的原因,

因为该大字段的值为空，9i连接会报错，连接 ',,'    不会报错