今日遇到一案例
我想要使用listagg將字串串起,但卻遇到錯誤訊息「串接而成的字串過長」....
所以發現了另一種語法xmlagg,成功將字串串起~
listagg
select name , LISTAGG(text,'')WITHIN GROUP(ORDER BY line)
from (
Select name ,replace(text,chr(10),'') text ,line
from ALL_SOURCE
where OWNER = 'TEST'
and type = 'FUNCTION'
order by name, line
)
group by name;
xmlagg
select name , xmlagg(xmlparse(content text || '' wellformed)ORDER BY line).getclobval()
from (
Select name ,replace(text,chr(10),'') text ,line
from ALL_SOURCE
where OWNER = 'TEST'
and type = 'FUNCTION'
order by name, line
)
group by name;
這邊補充一下,使用xmlagg從JAVA JDBC取出來的東西,會是一個「Clob」物件
所以如果我們是要取得他的字串的話,需要做一些轉換,如下
Clob clob = (Clob)rs.getObject(i);
rowData.put(rsmd.getColumnName(i), clob.getSubString((long)1, (int)clob.length()));
留言列表