Sys_Connect_By_Path字符汇聚函数在10.2.0.3上的BUG

将某列的多行数据汇聚为一个字符串时，Oracle的WM_CONCAT函数没有排序，
而且在11.2.0.3及10.2.0.5中返回值为VARCHAR2变更为CLOB，会导致SQL出错。

如果不想在程序中排序，可以使用Sys_Connect_By_Path汇聚函数（效率不高，ZL的可以使用自定义函数f_List2str），
目前发现，10.2.0.3上，这种方式下树型查询的BUG：字符拼接结果差一些记录。
在10.2.0.4以及11.2.0.4上不存在这个BUG。
要避免这个问题，查询条件尽可能放在子查询中，并且加rownum 来实体化子查询。

例：
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


1.下面这种方式，拼接出的字符差一条记录的值“开始时间”
SQL> Select Table_Name, Constraint_Name, LTrim(Max(Sys_Connect_By_Path(Column_Name, ',')), ',') Cons_Cols
  2  From User_Cons_Columns D
  3  Where Table_Name = '部门安排'
  4  Start With Position = 1
  5  Connect By Prior Position + 1 = Position And Prior Constraint_Name = Constraint_Name
  6  Group By Table_Name, Constraint_Name;
TABLE_NAME                     CONSTRAINT_NAME                CONS_COLS
------------------------------ ------------------------------ --------------------------------------------------------------------------------
部门安排                        部门安排_UQ_开始时间            部门ID,星期
部门安排                        部门安排_UQ_终止时间            部门ID,星期


2.改为子查询方式，仍然存在问题
SQL> Select Table_Name, Constraint_Name, LTrim(Max(Sys_Connect_By_Path(Column_Name, ',')), ',') Cons_Cols
  2  From (Select Table_Name, Constraint_Name, Column_Name, Position
  3         From User_Cons_Columns D
  4         Where Table_Name = '部门安排')
  5  Start With Position = 1
  6  Connect By Prior Position + 1 = Position And Prior Constraint_Name = Constraint_Name
  7  Group By Table_Name, Constraint_Name;
TABLE_NAME                     CONSTRAINT_NAME                CONS_COLS
------------------------------ ------------------------------ --------------------------------------------------------------------------------


部门安排                        部门安排_UQ_开始时间            部门ID,星期
部门安排                        部门安排_UQ_终止时间            部门ID,星期


3.在子查询中加rownum来实体化后，得到了正确的结果。
SQL> Select Table_Name, Constraint_Name, LTrim(Max(Sys_Connect_By_Path(Column_Name, ',')), ',') Cons_Cols
  2  From (Select Rownum, Table_Name, Constraint_Name, Column_Name, Position
  3         From User_Cons_Columns D
  4         Where Table_Name = '部门安排')
  5  Start With Position = 1
  6  Connect By Prior Position + 1 = Position And Prior Constraint_Name = Constraint_Name
  7  Group By Table_Name, Constraint_Name;
TABLE_NAME                     CONSTRAINT_NAME                CONS_COLS
------------------------------ ------------------------------ --------------------------------------------------------------------------------
部门安排                        部门安排_UQ_开始时间            部门ID,星期,开始时间
部门安排                        部门安排_UQ_终止时间            部门ID,星期,终止时间


4.优化为不使用group by和max函数，直接用oracle 10g开始提供的虚拟列Connect_By_Isleaf来过滤只取树形的末级
SQL> Select Table_Name, Constraint_Name, LTrim(Sys_Connect_By_Path(Column_Name, ','), ',') Cons_Cols
  2  From (Select Rownum, Table_Name, Constraint_Name, Column_Name, Position
  3         From User_Cons_Columns D
  4         Where Table_Name = '部门安排')
  5  Where Connect_By_Isleaf = 1
  6  Start With Position = 1
  7  Connect By Prior Position + 1 = Position And Prior Constraint_Name = Constraint_Name
  8  ;
TABLE_NAME                     CONSTRAINT_NAME                CONS_COLS
------------------------------ ------------------------------ --------------------------------------------------------------------------------
部门安排                        部门安排_UQ_开始时间            部门ID,星期,开始时间
部门安排                        部门安排_UQ_终止时间            部门ID,星期,终止时间

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/117319/viewspace-1839913/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/117319/viewspace-1839913/