wmsys.wm_concat 實現行列轉換問題

it_newbalance發表於2013-09-22

近期做了一次AIX上資料庫從11.2.0.1到11.2.0.3的版本升級測試，測試後，發現報表系統資料出現了欄位型別在升級過程中，發生了資料型別不一致問題，由varchar2 型別轉化成了clob型別，ORA-00932: 不一致的資料型別: 要求 - 得到的卻是 BLOB，經網上查詢查得使用wmsys.wm_concat物件實現行列轉換的方法引起，這種方法不被Oracle所推薦，因為WMSYS使用者用於Workspace Manager，其函式物件可能因版本而不同，這種變化在11.2.0.3及10.2.0.5中體現出來。原本WM_CONCAT函式返回值為VARCHAR2變更為CLOB。這一變化導致了很多程式的異常。

該函式可以實現行列轉換：

SQL> select wmsys.wm_concat(username) from dba_users;

WMSYS.WM_CONCAT(USERNAME)
--------------------------------------------------------------------------------
SYS,SYSTEM,YANGTK,TEST,OUTLN,MGMT_VIEW,FLOWS_FILES,MDSYS,ORDSYS,EXFSYS,DBSNMP,WM

在11.2.0.3中，其返回值型別變更為CLOB：

SQL> desc wmsys.wm_concat
FUNCTION wmsys.wm_concat RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P1 VARCHAR2 IN

Oracle建議使用者使用自定義函式來實現該功能，而不是使用WorkSpace的這個內部函式。

這個函式包含一個Type、Type Body、Function，可以參考Oracle的實現方式來實現這個函式。

請注意，在將資料庫從其他版本升級到10.2.0.5和11.2.0.3中時，必須注意到，這個函式的返回值型別變化。

例如如下一個系列的函式，可以幫助使用者構建自有的行列轉換函式：

SQL> create or replace TYPE en_concat_im
2 AUTHID CURRENT_USER AS OBJECT
3 (
4 CURR_STR VARCHAR2(32767),
5 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT en_concat_im) RETURN NUMBER,
6 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT en_concat_im,
7 P1 IN VARCHAR2) RETURN NUMBER,
8 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN en_concat_im,
9 RETURNVALUE OUT VARCHAR2,
10 FLAGS IN NUMBER)
11 RETURN NUMBER,
12 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT en_concat_im,
13 SCTX2 IN en_concat_im) RETURN NUMBER
14 );
15 /

Type created.

SQL>
SQL> create or replace TYPE BODY en_concat_im
2 IS
3 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT en_concat_im)
4 RETURN NUMBER
5 IS
6 BEGIN
7 SCTX := en_concat_im(NULL) ;
8 RETURN ODCICONST.SUCCESS;
9 END;
10 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT en_concat_im,
11 P1 IN VARCHAR2)
12 RETURN NUMBER
13 IS
14 BEGIN
15 IF(CURR_STR IS NOT NULL) THEN
16 CURR_STR := CURR_STR || ';' || P1;
17 ELSE
18 CURR_STR := P1;
19 END IF;
20 RETURN ODCICONST.SUCCESS;
21 END;
22 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN en_concat_im,
23 RETURNVALUE OUT VARCHAR2,
24 FLAGS IN NUMBER)
25 RETURN NUMBER
26 IS
27 BEGIN
28 RETURNVALUE := CURR_STR ;
29 RETURN ODCICONST.SUCCESS;
30 END;
31 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT en_concat_im,
32 SCTX2 IN en_concat_im)
33 RETURN NUMBER
34 IS
35 BEGIN
36 IF(SCTX2.CURR_STR IS NOT NULL) THEN
37 SELF.CURR_STR := SELF.CURR_STR || ';' || SCTX2.CURR_STR ;
38 END IF;
39 RETURN ODCICONST.SUCCESS;
40 END;
41 END;
42 /

Type body created.

SQL> create or replace FUNCTION en_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING en_concat_im ;
2 /

Function created.

SQL> select en_concat(username) from dba_users;

EN_CONCAT(USERNAME)
--------------------------------------------------------------------------------
SYS;SYSTEM;YANGTK;TEST;OUTLN;MGMT_VIEW;FLOWS_FILES;MDSYS;ORDSYS;EXFSYS;DBSNMP;WM
SYS;APPQOSSYS;APEX_030200;OWBSYS_AUDIT;ORDDATA;CTXSYS;ANONYMOUS;SYSMAN;XDB;ORDPL
UGINS;OWBSYS;SI_INFORMTN_SCHEMA;OLAPSYS;ORACLE_OCM;XS$NULL;MDDATA;DIP;APEX_PUBLI
C_USER;SPATIAL_CSW_ADMIN_USR;SPATIAL_WFS_ADMIN_USR

以上改變還存在一個潛在的效能風險，那就是使用CLOB時會用到臨時段，在高壓力系統下，臨時檔案的大量佔用會導致臨時表空間的迅速增長。使用10046事件跟蹤，可以清晰的看到這些變化，以下是使用10046事件跟蹤兩個查詢：

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> select wmsys.wm_concat(username) from dba_users;

WMSYS.WM_CONCAT(USERNAME)
--------------------------------------------------------------------------------
SYS,SYSTEM,YANGTK,TEST,OUTLN,MGMT_VIEW,FLOWS_FILES,MDSYS,ORDSYS,EXFSYS,DBSNMP,WM

SQL> select en_concat(username) from dba_users;

EN_CONCAT(USERNAME)
--------------------------------------------------------------------------------
SYS;SYSTEM;YANGTK;TEST;OUTLN;MGMT_VIEW;FLOWS_FILES;MDSYS;ORDSYS;EXFSYS;DBSNMP;WM
SYS;APPQOSSYS;APEX_030200;OWBSYS_AUDIT;ORDDATA;CTXSYS;ANONYMOUS;SYSMAN;XDB;ORDPL
UGINS;OWBSYS;SI_INFORMTN_SCHEMA;OLAPSYS;ORACLE_OCM;XS$NULL;MDDATA;DIP;APEX_PUBLI
C_USER;SPATIAL_CSW_ADMIN_USR;SPATIAL_WFS_ADMIN_USR

查詢日誌輸出如下，其中Disk file operations I/O 操作了檔案201，即臨時檔案：

=====================
PARSING IN CURSOR #139839047786312 len=47 dep=0 uid=0 ct=3 lid=0 tim=1346138299723909 hv=2250098298 ad='b855e660' sqlid='75n6afa31vjmu'
select wmsys.wm_concat(username) from dba_users
END OF STMT
PARSE #139839047786312:c=12999,e=12924,p=0,cr=107,cu=0,mis=1,r=0,dep=0,og=1,plh=4128086143,tim=1346138299723908
EXEC #139839047786312:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4128086143,tim=1346138299724042
WAIT #139839047786312: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299724094
WAIT #139839047786312: nam='SQL*Net message from client' ela= 126 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299724260
WAIT #139839047786312: nam='Disk file operations I/O' ela= 41 FileOperation=2 fileno=201 filetype=2 obj#=539 tim=1346138299726980
WAIT #139839047786312: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299727257
FETCH #139839047786312:c=2999,e=3001,p=0,cr=28,cu=17,mis=0,r=1,dep=0,og=1,plh=4128086143,tim=1346138299727281
STAT #139839047786312 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=28 pr=0 pw=0 time=2996 us)'
STAT #139839047786312 id=2 cnt=31 pid=1 pos=1 bj=0 p='HASH JOIN (cr=28 pr=0 pw=0 time=1903 us cost=22 size=1764 card=21)'
STAT #139839047786312 id=3 cnt=31 pid=2 pos=1 bj=0 p='HASH JOIN (cr=26 pr=0 pw=0 time=1815 us cost=20 size=1701 card=21)'
STAT #139839047786312 id=4 cnt=31 pid=3 pos=1 bj=0 p='HASH JOIN (cr=20 pr=0 pw=0 time=1622 us cost=16 size=1638 card=21)'
STAT #139839047786312 id=5 cnt=31 pid=4 pos=1 bj=0 p='HASH JOIN OUTER (cr=14 pr=0 pw=0 time=1442 us cost=13 size=1575 card=21)'
STAT #139839047786312 id=6 cnt=31 pid=5 pos=1 bj=0 p='HASH JOIN (cr=12 pr=0 pw=0 time=810 us cost=10 size=966 card=21)'
STAT #139839047786312 id=7 cnt=31 pid=6 pos=1 bj=0 p='HASH JOIN (cr=10 pr=0 pw=0 time=486 us cost=8 size=924 card=21)'
STAT #139839047786312 id=8 cnt=1 pid=7 pos=1 bj=0 p='MERGE JOIN CARTESIAN (cr=4 pr=0 pw=0 time=83 us cost=4 size=16 card=1)'
STAT #139839047786312 id=9 cnt=1 pid=8 pos=1 bj=281 p='TABLE ACCESS FULL PROFILE$ (cr=2 pr=0 pw=0 time=40 us cost=2 size=8 card=1)'
STAT #139839047786312 id=10 cnt=1 pid=8 pos=2 bj=0 p='BUFFER SORT (cr=2 pr=0 pw=0 time=32 us cost=2 size=8 card=1)'
STAT #139839047786312 id=11 cnt=1 pid=10 pos=1 bj=281 p='TABLE ACCESS FULL PROFILE$ (cr=2 pr=0 pw=0 time=15 us cost=2 size=8 card=1)'
STAT #139839047786312 id=12 cnt=31 pid=7 pos=2 bj=22 p='TABLE ACCESS FULL USER$ (cr=6 pr=0 pw=0 time=41 us cost=3 size=784 card=28)'
STAT #139839047786312 id=13 cnt=1 pid=6 pos=2 bj=282 p='TABLE ACCESS FULL PROFNAME$ (cr=2 pr=0 pw=0 time=11 us cost=2 size=2 card=1)'
STAT #139839047786312 id=14 cnt=2 pid=5 pos=2 bj=298 p='TABLE ACCESS FULL RESOURCE_GROUP_MAPPING$ (cr=2 pr=0 pw=0 time=16 us cost=2 size=29 card=1)'
STAT #139839047786312 id=15 cnt=5 pid=4 pos=2 bj=16 p='TABLE ACCESS FULL TS$ (cr=6 pr=0 pw=0 time=80 us cost=3 size=15 card=5)'
STAT #139839047786312 id=16 cnt=5 pid=3 pos=2 bj=16 p='TABLE ACCESS FULL TS$ (cr=6 pr=0 pw=0 time=10 us cost=3 size=15 card=5)'
STAT #139839047786312 id=17 cnt=9 pid=2 pos=2 bj=293 p='TABLE ACCESS FULL USER_ASTATUS_MAP (cr=2 pr=0 pw=0 time=16 us cost=2 size=27 card=9)'
WAIT #139839047786312: nam='SQL*Net message from client' ela= 123 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299727940
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299727989
LOBREAD: c=0,e=36,p=0,cr=2,cu=0,tim=1346138299728000
WAIT #0: nam='SQL*Net message from client' ela= 156 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299728176
LOBTMPFRE: c=0,e=44,p=0,cr=0,cu=5,tim=1346138299728297
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299728328
WAIT #0: nam='SQL*Net message from client' ela= 29 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299728367
FETCH #139839047786312:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=4128086143,tim=1346138299728385
WAIT #139839047786312: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299728399

*** 2012-08-28 15:18:25.570
WAIT #139839047786312: nam='SQL*Net message from client' ela= 5842346 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138305570755
CLOSE #139839047786312:c=0,e=11,dep=0,type=0,tim=1346138305570850
=====================
PARSING IN CURSOR #139839047799848 len=37 dep=1 uid=0 ct=3 lid=0 tim=1346138305571205 hv=1398610540 ad='c183e438' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #139839047799848:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1346138305571204
BINDS #139839047799848:
Bind#0
acdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=16 ff=0
kxsbbbfp=7f2ed0d08410 bln=16 avl=16 flg=05
value=00002294.0002.0001
EXEC #139839047799848:c=1000,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1346138305571319
FETCH #139839047799848:c=0,e=19,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1346138305571351
STAT #139839047799848 id=1 cnt=1 pid=0 pos=1 bj=69 p='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=13 us cost=1 size=15 card=1)'
CLOSE #139839047799848:c=0,e=30,dep=1,type=0,tim=1346138305571394
=====================
PARSING IN CURSOR #139839047786312 len=41 dep=0 uid=0 ct=3 lid=0 tim=1346138305582448 hv=4228787766 ad='b8517a08' sqlid='75b6jnvy0wbjq'
select en_concat(username) from dba_users
END OF STMT
PARSE #139839047786312:c=10999,e=11557,p=0,cr=98,cu=0,mis=1,r=0,dep=0,og=1,plh=4128086143,tim=1346138305582447
EXEC #139839047786312:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4128086143,tim=1346138305582560
WAIT #139839047786312: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138305582592
FETCH #139839047786312:c=2000,e=2480,p=0,cr=28,cu=0,mis=0,r=1,dep=0,og=1,plh=4128086143,tim=1346138305585093
STAT #139839047786312 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=28 pr=0 pw=0 time=2493 us)'
STAT #139839047786312 id=2 cnt=31 pid=1 pos=1 bj=0 p='HASH JOIN (cr=28 pr=0 pw=0 time=1979 us cost=22 size=1764 card=21)'
STAT #139839047786312 id=3 cnt=31 pid=2 pos=1 bj=0 p='HASH JOIN (cr=26 pr=0 pw=0 time=1842 us cost=20 size=1701 card=21)'
STAT #139839047786312 id=4 cnt=31 pid=3 pos=1 bj=0 p='HASH JOIN (cr=20 pr=0 pw=0 time=1669 us cost=16 size=1638 card=21)'
STAT #139839047786312 id=5 cnt=31 pid=4 pos=1 bj=0 p='HASH JOIN OUTER (cr=14 pr=0 pw=0 time=1517 us cost=13 size=1575 card=21)'
STAT #139839047786312 id=6 cnt=31 pid=5 pos=1 bj=0 p='HASH JOIN (cr=12 pr=0 pw=0 time=839 us cost=10 size=966 card=21)'
STAT #139839047786312 id=7 cnt=31 pid=6 pos=1 bj=0 p='HASH JOIN (cr=10 pr=0 pw=0 time=490 us cost=8 size=924 card=21)'
STAT #139839047786312 id=8 cnt=1 pid=7 pos=1 bj=0 p='MERGE JOIN CARTESIAN (cr=4 pr=0 pw=0 time=110 us cost=4 size=16 card=1)'
STAT #139839047786312 id=9 cnt=1 pid=8 pos=1 bj=281 p='TABLE ACCESS FULL PROFILE$ (cr=2 pr=0 pw=0 time=69 us cost=2 size=8 card=1)'
STAT #139839047786312 id=10 cnt=1 pid=8 pos=2 bj=0 p='BUFFER SORT (cr=2 pr=0 pw=0 time=35 us cost=2 size=8 card=1)'
STAT #139839047786312 id=11 cnt=1 pid=10 pos=1 bj=281 p='TABLE ACCESS FULL PROFILE$ (cr=2 pr=0 pw=0 time=17 us cost=2 size=8 card=1)'
STAT #139839047786312 id=12 cnt=31 pid=7 pos=2 bj=22 p='TABLE ACCESS FULL USER$ (cr=6 pr=0 pw=0 time=40 us cost=3 size=784 card=28)'
STAT #139839047786312 id=13 cnt=1 pid=6 pos=2 bj=282 p='TABLE ACCESS FULL PROFNAME$ (cr=2 pr=0 pw=0 time=9 us cost=2 size=2 card=1)'
STAT #139839047786312 id=14 cnt=2 pid=5 pos=2 bj=298 p='TABLE ACCESS FULL RESOURCE_GROUP_MAPPING$ (cr=2 pr=0 pw=0 time=14 us cost=2 size=29 card=1)'
STAT #139839047786312 id=15 cnt=5 pid=4 pos=2 bj=16 p='TABLE ACCESS FULL TS$ (cr=6 pr=0 pw=0 time=28 us cost=3 size=15 card=5)'
STAT #139839047786312 id=16 cnt=5 pid=3 pos=2 bj=16 p='TABLE ACCESS FULL TS$ (cr=6 pr=0 pw=0 time=13 us cost=3 size=15 card=5)'
STAT #139839047786312 id=17 cnt=9 pid=2 pos=2 bj=293 p='TABLE ACCESS FULL USER_ASTATUS_MAP (cr=2 pr=0 pw=0 time=8 us cost=2 size=27 card=9)'
WAIT #139839047786312: nam='SQL*Net message from client' ela= 193 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138305585818
FETCH #139839047786312:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=4128086143,tim=1346138305585841
WAIT #139839047786312: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138305585855

*** 2012-08-28 15:18:34.648
WAIT #139839047786312: nam='SQL*Net message from client' ela= 9062490 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138314648354
CLOSE #139839047786312:c=0,e=10,dep=0,type=0,tim=1346138314648443
=====================

以上問題在版本升級時需要注意。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24558279/viewspace-772965/，如需轉載，請註明出處，否則將追究法律責任。

警示:通過 wmsys.wm_concat 實現行列轉換
2015-03-17
行列轉換問題總結
2010-09-03
wmsys.wm_concat、sys_connect_by_path、自定義函式實現行列轉換
2010-11-17
函式
【轉】Oracle: wmsys.wm_concat、sys_connect_by_path、自定義函式實現行列轉換
2012-04-22
Oracle函式
Oracle10g的行列轉換 wmsys.wm_concat
2011-01-13
Oracle
pivot、unpivot實現oracle行列轉換
2015-07-16
Oracle
sql 實現表的行列轉換
2011-03-29
SQL
Spark實現行列轉換pivot和unpivot
2019-03-03
Spark
WORD及EXCEL行列轉換實現方法收藏(轉)
2009-05-15
Excel
SQL Server 實現行列（縱橫表）轉換
2015-03-28
SQLServer
用ORACLE分析函式實現行列轉換
2010-08-18
Oracle函式
試驗Oracle中實現行列轉換的方法（轉）
2009-04-13
Oracle
行列轉換
2011-01-25
使用vue實現行列轉換的一種方法。
2019-08-06
Vue
在Word中實現表格的行列互換 (轉)
2007-12-14
Kettle行列轉換
2015-09-01
偽行列轉換！
2011-07-17
行列轉換sql
2008-02-20
SQL
用abap實現內表的行列轉換-原始碼2 （轉）
2007-04-14
原始碼
Oracle--SQL行列轉換實戰
2006-06-02
OracleSQL
行列轉換交叉表 (轉)
2007-08-17
Oracle-行列轉換
2017-03-07
Oracle
MySQL行列轉換拼接
2015-07-20
MySql
行列轉換之大全~~~
2007-04-19
sql server 行列轉換
2009-12-15
SQLServer
報表如何實現行列互換效果？
2019-12-17
oracle行列轉換-多行轉換成字串
2014-01-03
Oracle字串
行列轉換，列行轉換統計
2013-06-24
在SQL Server 2005中實現表的行列轉換
2006-06-12
SQLServer
oracle行列轉換-行轉列
2014-01-03
Oracle
oracle行列轉換-列轉行
2014-01-03
Oracle
關於分組後欄位拼接的問題[行列轉換]『By duanzilin』
2005-10-10
Restcloud ETl實踐之資料行列轉換
2022-06-28
RESTCloud
幾個行列轉換的實用小例子
2015-01-17
mysql行列轉換詳解
2021-01-03
MySql
sql server行列轉換案例
2016-10-18
SQLServer
Oracle 行列轉換經典
2013-12-12
Oracle
Oracle 行列轉換總結
2013-06-21
Oracle

wmsys.wm_concat 實現行列轉換問題

相關文章