wmsys.wm_concat 超長問題 解決方案

原文：  https://blog.csdn.net/xmm_1030/article/details/46724153

使用wmsys.wm_concat多列合成一列遇到問題
ORA-22813: 運算元值超出系統的限制

10G  總長度不能超過 4k 。 

已經寫了儲存過程作為替代方案了

1. CREATE OR REPLACE TYPE zh_concat_im
2. AUTHID CURRENT_USER AS OBJECT
3. (
4. CURR_STR clob,
5. STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER,
6. MEMBER FUNCTION ODCIAGGREGATEITERATE( SELF IN OUT zh_concat_im,
7. P1 IN VARCHAR2) RETURN NUMBER,
8. MEMBER FUNCTION ODCIAGGREGATETERMINATE( SELF IN zh_concat_im,
9. RETURNVALUE OUT clob,
10. FLAGS IN NUMBER)
11. RETURN NUMBER,
12. MEMBER FUNCTION ODCIAGGREGATEMERGE( SELF IN OUT zh_concat_im,
13. SCTX2 IN zh_concat_im) RETURN NUMBER
14. );
16. CREATE OR REPLACE TYPE BODY zh_concat_im
17. IS
18. STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)
19. RETURN NUMBER
20. IS
21. BEGIN
22. SCTX := zh_concat_im( NULL) ;
23. RETURN ODCICONST.SUCCESS;
24. END;
25. MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
27. P1 IN VARCHAR2)
28. RETURN NUMBER
29. IS
30. BEGIN
31. IF(CURR_STR IS NOT NULL) THEN
32. CURR_STR := CURR_STR || ',' || P1;
33. ELSE
34. CURR_STR := P1;
35. END IF;
36. RETURN ODCICONST.SUCCESS;
37. END;
38. MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
39. RETURNVALUE OUT clob,
40. FLAGS IN NUMBER)
41. RETURN NUMBER
42. IS
43. BEGIN
44. RETURNVALUE := CURR_STR ;
45. RETURN ODCICONST.SUCCESS;
46. END;
47. MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
48. SCTX2 IN zh_concat_im)
49. RETURN NUMBER
50. IS
51. BEGIN
52. IF(SCTX2.CURR_STR IS NOT NULL) THEN
53. SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
54. END IF;
55. RETURN ODCICONST.SUCCESS;
56. END;
57. END;
59. create or replace FUNCTION zh_concat(P1 VARCHAR2)
60. RETURN clob AGGREGATE USING zh_concat_im ;