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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章