« Oracle Database 12c 新特性 - Pluggable Database(转) | Blog首页 | ACOUG 2012 Oracle技术嘉年华活动结束 »

警示:通过 wmsys.wm_concat 实现行列转换

在网上流传很多文章，描述了使用wmsys.wm_concat对象实现行列转换的方法，这种方法不被Oracle所推荐，因为WMSYS用户用于Workspace Manager，其函数对象可能因版本而不同，这种变化在11.2.0.3及10.2.0.5中体现出来。原本WM_CONCAT函数返回值为VARCHAR2变更为CLOB。这一变化导致了很多程序的异常。

SQL> desc wmsys.wm_concat

FUNCTION wmsys.wm_concat RETURNS CLOB

 Argument Name Type In/Out Default?

 ------------------------------ ----------------------- ------ --------

 P1 VARCHAR2 IN

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

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

=====================

PARSING IN CURSOR #139839047786312 len=47 dep=0 uid=0 oct=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 obj=0 op='SORT AGGREGATE (cr=28 pr=0 pw=0 time=2996 us)'

STAT #139839047786312 id=2 cnt=31 pid=1 pos=1 obj=0 op='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 obj=0 op='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 obj=0 op='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 obj=0 op='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 obj=0 op='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 obj=0 op='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 obj=0 op='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 obj=281 op='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 obj=0 op='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 obj=281 op='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 obj=22 op='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 obj=282 op='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 obj=298 op='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 obj=16 op='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 obj=16 op='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 obj=293 op='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 oct=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

  oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=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 obj=69 op='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 oct=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 obj=0 op='SORT AGGREGATE (cr=28 pr=0 pw=0 time=2493 us)'

STAT #139839047786312 id=2 cnt=31 pid=1 pos=1 obj=0 op='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 obj=0 op='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 obj=0 op='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 obj=0 op='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 obj=0 op='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 obj=0 op='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 obj=0 op='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 obj=281 op='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 obj=0 op='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 obj=281 op='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 obj=22 op='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 obj=282 op='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 obj=298 op='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 obj=16 op='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 obj=16 op='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 obj=293 op='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

=====================

By eygle on 2012-10-31 19:13 | Comments (0) | SQL.PLSQL | 3040 |

PackageSQL