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连接会报错,连接 ',,' 不会报错