【函數】wm_concat包的訂制
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① 利用系統包創建WM_CONCAT函數（重點）
② ORA-00904: "wm_concat":invalid identifier錯誤解決
③ 訂制自己的WM_CONCAT函數
④ listagg分析函數的使用
⑤ ORA-01489: result of string concatenation is too long的錯誤解決
Tips：
① 本文在itpub（http://blog.itpub.net/26736162）、博客園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）有同步更新。
② 文章中用到的所有代碼，相關軟件，相關資料請前往小麥苗的雲盤下載（http://blog.itpub.net/26736162/viewspace-1624453/）。
③ 若網頁文章代碼格式有錯亂，推薦使用360浏覽器，也可以下載pdf格式的文檔來查看，pdf文檔下載地址：http://blog.itpub.net/26736162/viewspace-1624453/，另外itpub格式顯示有問題，也可以去博客園地址閱讀。
④ 本篇BLOG中命令的輸出部分需要特別關注的地方我都用灰色背景和粉紅色字體來表示，比如下邊的例子中，thread 1的最大歸檔日志號為33，thread 2的最大歸檔日志號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字體標注；對代碼或代碼輸出部分的注釋一般采用藍色字體表示。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[ZHLHRDB1:root]:/>lsvg -o
T_XLHRD_APP1_vg
rootvg
[ZHLHRDB1:root]:/>
00:27:22 SQL> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1G
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
2.2 相關參考文章鏈接
行轉列參考文章：http://blog.itpub.net/26736162/viewspace-1272538/
WMSYS用戶下的WM_CONCAT函數有很重要的用途，比如行轉列（http://blog.itpub.net/26736162/viewspace-1272538/），但是該函數不穩定，在10G和11GR2上返回值不同，一個是字符串一個是CLOB，而且12C上已經摒棄了WM_CONCAT函數，但是我們很多程序員在程序中使用了該函數，若是系統升級就會導致程序出現錯誤，為了減輕程序員修改程序的工作量，只有創建這個WM_CONCAT函數來解決該問題。
一般情況下報錯信息，ORA-00904: "wm_concat":invalid identifier，查詢DBA_OBJECTS視圖，也未發現wm_concat的相關信息。正常情況下查詢，
SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE 'WM_CONCAT%';
應如下所示：
解決辦法有2種，一種是采用Oracle本身的腳本來創建WM_CONCAT函數，一種是采用自己創建的函數來完成這個功能。
運行如下腳本卸載WMSYS：
@$ORACLE_HOME/rdbms/admin/owmuinst.plb
運行如下腳本執行安裝WMSYS：
@$ORACLE_HOME/rdbms/admin/owminst.plb
解鎖wmsys用戶：
ALTER USER WMSYS ACCOUNT UNLOCK;
若只是某個用戶使用，那麼我們可以不用刻意去創建wmsys用戶，可以在當前用戶下運行腳本，生成WM_CONCAT函數，為了和系統的函數名區別開來，我們也可以修改函數名稱，訂制自己的腳本。
創建函數的腳本如下：
CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB_NULL_LHR AUTHID CURRENT_USER AS OBJECT
(
CURR_STR CLOB,
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
P1 IN CLOB) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL_CLOB_NULL_LHR,
RETURNVALUE OUT CLOB,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
SCTX2 IN WM_CONCAT_IMPL_CLOB_NULL_LHR)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB_NULL_LHR IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR)
RETURN NUMBER IS
BEGIN
SCTX := WM_CONCAT_IMPL_CLOB_NULL_LHR(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
P1 IN CLOB) RETURN NUMBER IS
BEGIN
IF (CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL_CLOB_NULL_LHR,
RETURNVALUE OUT CLOB,
FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := CURR_STR;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
SCTX2 IN WM_CONCAT_IMPL_CLOB_NULL_LHR)
RETURN NUMBER IS
BEGIN
IF (SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || SCTX2.CURR_STR;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
/
CREATE OR REPLACE FUNCTION WM_CONCAT_CLOB_NULL_LHR(P1 VARCHAR2) RETURN CLOB
AGGREGATE USING WM_CONCAT_IMPL_CLOB_NULL_LHR;
/
CREATE PUBLIC SYNONYM WM_CONCAT_CLOB_NULL_LHR FOR WM_CONCAT_CLOB_NULL_LHR;
GRANT EXECUTE ON WM_CONCAT_CLOB_NULL_LHR TO PUBLIC;
測試案例，注意函數的返回值是無分隔符的CLOB，在PL/SQL中注意使用to_char進行轉換：
SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
USER_ID
----------
0
5
SYS@lhrdb21> SELECT WM_CONCAT_CLOB_NULL_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
WM_CONCAT_CLOB_LHR_NULL(D.USER_ID)
--------------------------------------------------------------------------------
05
SYS@lhrdb21>
創建函數的腳本如下：
CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB_LHR AUTHID CURRENT_USER AS OBJECT
(
CURR_STR CLOB,
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_LHR)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR,
P1 IN CLOB) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL_CLOB_LHR,
RETURNVALUE OUT CLOB,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR,
SCTX2 IN WM_CONCAT_IMPL_CLOB_LHR)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB_LHR IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_LHR)
RETURN NUMBER IS
BEGIN
SCTX := WM_CONCAT_IMPL_CLOB_LHR(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR,
P1 IN CLOB) RETURN NUMBER IS
BEGIN
IF (CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ',' || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL_CLOB_LHR,
RETURNVALUE OUT CLOB,
FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := CURR_STR;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR,
SCTX2 IN WM_CONCAT_IMPL_CLOB_LHR)
RETURN NUMBER IS
BEGIN
IF (SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
/
CREATE OR REPLACE FUNCTION WM_CONCAT_CLOB_LHR(P1 VARCHAR2) RETURN CLOB
AGGREGATE USING WM_CONCAT_IMPL_CLOB_LHR;
/
CREATE PUBLIC SYNONYM WM_CONCAT_CLOB_LHR FOR WM_CONCAT_CLOB_LHR;
GRANT EXECUTE ON WM_CONCAT_CLOB_LHR TO PUBLIC;
測試案例，注意函數的返回值是以逗號為分隔符的CLOB，在PL/SQL中注意使用to_char進行轉換：
SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
USER_ID
----------
0
5
SYS@lhrdb21> SELECT WM_CONCAT_CLOB_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
WM_CONCAT_LHR(D.USER_ID)
--------------------------------------------------------------------------------
0,5
創建函數的腳本如下：
CREATE OR REPLACE TYPE WM_CONCAT_IMPL_STRINGS_LHR AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_STRINGS_LHR)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL_STRINGS_LHR,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
SCTX2 IN WM_CONCAT_IMPL_STRINGS_LHR)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_STRINGS_LHR IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_STRINGS_LHR)
RETURN NUMBER IS
BEGIN
SCTX := WM_CONCAT_IMPL_STRINGS_LHR(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
P1 IN VARCHAR2) RETURN NUMBER IS
BEGIN
IF (CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ',' || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL_STRINGS_LHR,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := CURR_STR;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
SCTX2 IN WM_CONCAT_IMPL_STRINGS_LHR)
RETURN NUMBER IS
BEGIN
IF (SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
/
CREATE OR REPLACE FUNCTION WM_CONCAT_STRINGS_LHR(P1 VARCHAR2) RETURN VARCHAR2
AGGREGATE USING WM_CONCAT_IMPL_STRINGS_LHR;
/
CREATE PUBLIC SYNONYM WM_CONCAT_STRINGS_LHR FOR WM_CONCAT_STRINGS_LHR;
GRANT EXECUTE ON WM_CONCAT_STRINGS_LHR TO PUBLIC;
測試案例，注意函數的返回值是以逗號為分隔符的字符串：
SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
USER_ID
----------
0
5
SYS@lhrdb21> SELECT WM_CONCAT_STRINGS_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
WM_CONCAT_STRINGS_LHR(D.USER_ID)
---------------------------------------------------
0,5
這是一個Oracle的列轉行函數：LISTAGG()
with temp as(
select 'China' nation ,'Guangzhou' city from dual union all
select 'China' nation ,'Shanghai' city from dual union all
select 'China' nation ,'Beijing' city from dual union all
select 'USA' nation ,'New York' city from dual union all
select 'USA' nation ,'Bostom' city from dual union all
select 'USA' nation ,'Bostom' city from dual union all
select 'Japan' nation ,'Tokyo' city from dual
)
select nation,listagg(city,',') within GROUP (order by city)
from temp
group by nation;
但是如果聚合的內容太多就會報ORA-01489: result of string concatenation is too long的錯誤，這個時候可以從業務的角度去修改SQL，也可以使用WM_CONCAT函數返回CLOB類型來解決這個問題。如下聚合DBA_OBJECTS中的OBJECT_NAME就會報錯：
SELECT LISTAGG(OBJECT_NAME, ',') WITHIN GROUP(ORDER BY OBJECT_NAME)
FROM DBA_OBJECTS D;
報錯：ORA-01489: result of string concatenation is too long
解決：可以用WM_CONCAT返回CLOB類型即可。SELECT WM_CONCAT_CLOB_LHR(D.OBJECT_NAME) FROM DBA_OBJECTS D;
注意：有關WM_CONCAT函數返回CLOB類型的性能問題，我們本篇文章不討論，聚合的內容多了，自然就慢，到底是避免出ORA-01489錯誤還是要結果，這個還得根據自己的情況權衡決定，比如有的系統tmp很大，隨便用，那作為開發人員，估計才不會考慮這麼多的，不管白貓黑貓，抓住老鼠就是好貓。
About Me
...............................................................................................................................
● 本文作者：小麥苗，只專注於數據庫的技術，更注重技術的運用
● 本文在itpub（http://blog.itpub.net/26736162）、博客園(http://www.cnblogs.com/lhrbest)和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2124931/
● 本文博客園地址：http://www.cnblogs.com/lhrbest/p/5869463.html
● 本文pdf版：http://yunpan.cn/cdEQedhCs2kFz （提取碼：ed9b）
● 小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● QQ群：230161599 微信群：私聊
● 聯系我請加QQ好友(642808185)，注明添加緣由
● 於 2016-09-13 09:00~ 2016-09-13 11:30 在中行完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網絡，若有侵權或不當之處還請諒解！
● 【版權所有，文章允許轉載，但須以鏈接方式注明源地址，否則追究法律責任】
...............................................................................................................................
手機長按下圖識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號：xiaomaimiaolhr,免費學習最實用的數據庫技術。
