from http://blog.itpub.net/4227/viewspace-588644/
分类： Linux
在我的BLOG中第一篇文章写的就是字符串聚合连接的例子：http://yangtingkun.itpub.net/post/468/3380。
后来还写过一篇用SQL实现相同功能的文章：http://yangtingkun.itpub.net/post/468/388003。
不过上面两种方法都会面临一个问题，就是如果聚集连接的字符串长度如果超过了VARCHAR2类型所允许的最大长度，就会导致字符串超长的错误。
自定义聚集函数这里就不重复了，可以参考上面的链接：
SQL> SELECT F_LINK(TNAME) FROM TAB;
F_LINK(TNAME)
-------------------------------------------------------------------------------------------
BAK_SHGOV_ORDER,BAK_SHGOV_ORDER_BAK,PLAN_TABLE,SHGOV_ORDER,SHGOV_ORDER_BAK,T,T1,TEST,T_SQL
SQL> SELECT F_LINK(SEQUENCE_NAME) FROM ALL_SEQUENCES;
SELECT F_LINK(SEQUENCE_NAME) FROM ALL_SEQUENCES
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在"TEST.T_LINK", line 16
ORA-06512: 在line 1
SQL> SELECT MAX(LTRIM(SYS_CONNECT_BY_PATH(SEQUENCE_NAME, ','), ',')) NAME
2 FROM
3 (
4 SELECT SEQUENCE_NAME, ROW_NUMBER() OVER(ORDER BY SEQUENCE_NAME) RN
5 FROM ALL_SEQUENCES
6 )
7 START WITH RN = 1
8 CONNECT BY PRIOR RN + 1 = RN
9 ;
FROM ALL_SEQUENCES
*
ERROR 位于第 5 行:
ORA-01489: 字符串连接的结果过长
显然是由于要连接的字符串太长了，导致Oracle的字符串处理过程中出现了错误。上面的两种方法都没有办法避免这个问题。
但是ALL_SEQUENCES中的记录只有几百个，每个名称的长度不会超过30，因此最终的长度不会超过32767。
根据Oracle给出的错误信息，显然是在处理输出参数RETURNVALUE的时候是安装SQL类型的VARCHAR2长度4000做的限制，那么只需要修改输出参数和聚集函数的返回值类型为CLOB类型即可：
SQL> CREATE OR REPLACE TYPE T_LINK AS OBJECT (
2 STR VARCHAR2(32767),
3 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER,
4 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
5 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER,
6 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER
7 )
8 /
类型已创建。
SQL> CREATE OR REPLACE TYPE BODY T_LINK IS
2 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER IS
3 BEGIN
4 SCTX := T_LINK(NULL);
5 RETURN ODCICONST.SUCCESS;
6 END;
7
8 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
9 BEGIN
10 SELF.STR := SELF.STR || VALUE || ',';
11 RETURN ODCICONST.SUCCESS;
12 END;
13
14 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER IS
15 BEGIN
16 RETURNVALUE := SUBSTR(SELF.STR, 1, LENGTH(SELF.STR) - 1);
17 RETURN ODCICONST.SUCCESS;
18 END;
19
20 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS
21 BEGIN
22 NULL;
23 RETURN ODCICONST.SUCCESS;
24 END;
25 END;
26 /
类型主体已创建。
SQL> CREATE OR REPLACE FUNCTION F_LINK(P_STR VARCHAR2) RETURN CLOB
2 AGGREGATE USING T_LINK;
3 /
函数已创建。
SQL> SELECT F_LINK(SEQUENCE_NAME) FROM ALL_SEQUENCES;
F_LINK(SEQUENCE_NAME)
--------------------------------------------------------------------------------
APPLY$_DEST_OBJ_ID,APPLY$_ERROR_HANDLER_SEQUENCE,APPLY$_SOURCE_OBJ_ID,AQ$_CHAINS
EQ,AQ$_IOTENQTXID,AQ$_PROPAGATION_SEQUENCE,AQ$_PUBLISHER_SEQUENCE,AQ$_RULE_SEQUE
NCE,AQ$_RULE_SET_SEQUENCE,AQ$_TRANS_SEQUENCE,AUDSES$,AWSEQ$,CDC_RSID_SEQ$,CDC_SU
BSCRIBE_SEQ$,DBMS_LOCK_ID,GENERATOR$_S,HS$_BASE_DD_S,HS$_CLASS_CAPS_S,HS$_CLASS_
DD_S,HS$_CLASS_INIT_S,HS$_FDS_CLASS_S,HS$_FDS_INST_S,HS$_INST_CAPS_S,HS$_INST_DD
_S,HS$_INST_INIT_S,IDGEN1$,JAVA$POLICY$SEQUENCE$,JOBSEQ,LOG$SEQUENCE,OBJECT_GRAN
T,ORA_TQ_BASE$,PARTITION_NAME$,PROFNUM$,PSINDEX_SEQ$,RGROUPSEQ,SNAPSHOT_ID$,SNAP
SITE_ID$,STREAMS$_CAPTURE_INST,STREAMS$_RULE_NAME_S,SYSTEM_GRANT,UGROUP_SEQUENCE
,LOGMNR_SEQ$,LOGMNR_UIDS$,MVIEW$_ADVSEQ_GENERIC,MVIEW$_ADVSEQ_ID,REPCAT$_EXCEPTI
ONS_S,REPCAT$_FLAVORS_S,REPCAT$_FLAVOR_NAME_S,REPCAT$_REFRESH_TEMPLATES_S,REPCAT
$_REPPROP_KEY,REPCAT$_RUNTIME_PARMS_S,REPCAT$_TEMPLATE_OBJECTS_S,REPCAT$_TEMPLAT
E_PARMS_S,REPCAT$_TEMPLATE_REFGROUPS_S,REPCAT$_TEMPLATE_SITES_S,REPCAT$_TEMP_OUT
PUT_S,REPCAT$_USER_AUTHORIZATIONS_S,REPCAT$_USER_PARM_VALUES_S,REPCAT_LOG_SEQUEN
CE,TEMPLATE$_TARGETS_S,WM$ADT_SEQUENCE,WM$INSTEADOF_TRIGS_SEQUENCE,WM$LOCK_SEQUE
NCE,WM$NESTED_COLUMNS_SEQ,WM$ROW_SYNC_ID_SEQUENCE,WM$UDTRIG_DISPATCHER_SEQUENCE,
WM$UP_DEL_TRIG_NAME_SEQUENCE,WM$VERSION_SEQUENCE,WM$VTID,SAMPLE_SEQ,SDO_IDX_TAB_
SEQUENCE,DR_ID_SEQ,MESG_ID_SEQ,THS_SEQ,XDB$NAMESUFF_SEQ,XDB$PROPNUM_SEQ,WK$CHARS
ET_SEQ,WK$CRAWLERID_SEQ,WK$DATA_SOURCE_PARAM_SEQ,WK$DOC_ATTR_SEQ,WK$DSPARAM_ID_S
EQ,WK$DSTYPE_ID_SEQ,WK$DS_ID_SEQ,WK$INST_SEQ,WK$JOB_ID_SEQ,WK$MAILLIST$SEQ,WK$MI
METYPES_SEQ,WK$PT_ID_SEQ,WK$SCHED_ID_SEQ,WK$SEARCH_ATTR_SEQ,WK$SG_ID_SEQ,WK$SUBS
CRIBER_SEQ,WK$TRACE_SEQ,WK$UNIQUE_STRINGID_SEQ,SEQ_CLASSIFICATION_RESULT_ID,SEQ_
LIFT_RESULT_ID,SEQ_MATRIX_ENTRY_ID,SEQ_MS_RESULT_ENTRY_ID,SEQ_MS_RESULT_ID,SEQ_O
DM_MESSAGE_LOG_ID,SEQ_ODM_MINING_TASK_ID,SEQ_ODM_UNIQUE_OBJECT_ID,SEQ_TEST_RESUL
T_ID,UNIQUE_ID,CWM2_OLAP_ENABLESEQ,DBMS_MVSEQ,OLAP_ID_SEQ,OLAP_IRID,DEPARTMENTS_
SEQ,EMPLOYEES_SEQ,LOCATIONS_SEQ,ORDERS_SEQ,AQ$_AQ$_MEM_MC_N,AQ$_QS_ORDERS_PR_MQT
AB_N,AQ$_QS_WS_ORDERS_MQTAB_N,AQ$_QS_WS_ORDERS_PR_MQTAB_N,AQ$_QS_ES_ORDERS_MQTAB
_N,AQ$_QS_ES_ORDERS_PR_MQTAB_N,AQ$_QS_OS_ORDERS_MQTAB_N,AQ$_QS_OS_ORDERS_PR_MQTA
B_N,AQ$_QS_CBADM_ORDERS_MQTAB_N,AQ$_QS_CS_ORDER_STATUS_QT_N,GOV_SEQ,HI_TEMPTOTAL
_ID,IMP_SYS_TMP,JOB_ID,MICROSOFTSEQDTPROPERTIES,SEQZ_USER_INFO,SEQ_CAT_MANAGER_B
UYER,SEQ_CON_LIST_ITEM_SHARE,SEQ_EMED_WEB_LOG,SEQ_TEMP_JP,SMS_ID_SEQ,SP_BID,SP_B
ID_ITEM,SP_INVITE_ITEM,SY_INVITE_ITEM,S_AD,S_BID,S_BIDORG_EFFECT,S_BID_ITEM,S_BI
D_ITEM_TEMP,S_BID_PRODUCT,S_BID_TEMP,S_DISCUSS_EXPERT,S_ENTER_COMM,S_EXPERT_BIDO
RG,S_EXPERT_ELLECT,S_EXPERT_ENTER,S_EXPERT_FACTORY,S_EXPERT_ITEM,S_EXPERT_PRODUC
T,S_EXPERT_SALE,S_EXPERT_SCHEME,S_FACTORY_EFFECT,S_ID,S_INVITE_APPRAISE,S_INVITE
_APPRAISE_ITEM,S_INVITE_COMM,S_INVITE_EXPERT,S_INVITE_ITEM,S_INVITE_PRODUCT_AFFI
X,S_INVITE_SALER_AFFIX,S_JD_AREA,S_JD_REFERENCE_PRICE,S_JD_SCHEME_MODEL,S_JD_SEN
D_ORG,S_JD_SEND_SCHEME,S_MESSAGE,S_PLAT_PRODUCT_6,S_PRE_BID_ITEM,S_PRODUCT_EFFEC
T,S_PRODUCT_FORP,S_PURCHASE_CODE,S_SALE_EFFECT,S_SCHEME_EFFECT,TMP_SYS_IMP,TRANF
ER,TRANSFER,STATS$SNAPSHOT_ID,GOV_SEQ,HI_TEMPTOTAL_ID,MICROSOFTSEQDTPROPERTIES,S
EQZ_USER_INFO,SEQ_TEMP_JP,SMS_ID_SEQ,SP_BID_ITEM,SP_INVITE_ITEM,SYS_DATA_ID,SY_I
NVITE_ITEM,S_AD,S_BID,S_BIDORG_EFFECT,S_BID_ITEM,S_BID_ITEM_TEMP,S_BID_PRODUCT,S
_BID_TEMP,S_DISCUSS_EXPERT,S_ENTER_COMM,S_EXPERT_BIDORG,S_EXPERT_ELLECT,S_EXPERT
_ENTER,S_EXPERT_FACTORY,S_EXPERT_ITEM,S_EXPERT_PRODUCT,S_EXPERT_SCHEME,S_FACTORY
_EFFECT,S_HIS_INV,S_ID,S_INVITE_APPRAISE,S_INVITE_APPRAISE_ITEM,S_INVITE_COMM,S_
INVITE_EXPERT,S_INVITE_ITEM,S_INVITE_PRODUCT_AFFIX,S_INVITE_SALER_AFFIX,S_JD_ARE
A,S_JD_REFERENCE_PRICE,S_JD_SCHEME_MODEL,S_JD_SEND_ORG,S_JD_SEND_SCHEME,S_MESSAG
E,S_PLAT_PRODUCT_6,S_PRODUCT,S_INFO_ID,S_PIC,S_USER_ID,S_INFO_ID,S_PIC,S_USER_ID
,S_ID,S_SEQ,GOV_SEQ,HI_TEMPTOTAL_ID,MICROSOFTSEQDTPROPERTIES,SEQZ_USER_INFO,SEQ_
TEMP_JP,SMS_ID_SEQ,SP_BID_ITEM,SP_INVITE_ITEM,SYS_DATA_ID,SY_INVITE_ITEM,S_AD,S_
BID,S_BIDORG_EFFECT,S_BID_ITEM,S_BID_ITEM_TEMP,S_BID_PRODUCT,S_BID_TEMP,S_DISCUS
S_EXPERT,S_ENTER_COMM,S_EXPERT_BIDORG,S_EXPERT_ELLECT,S_EXPERT_ENTER,S_EXPERT_FA
CTORY,S_EXPERT_ITEM,S_EXPERT_PRODUCT,S_EXPERT_SCHEME,S_FACTORY_EFFECT,S_HIS_INV,
S_ID,S_INVITE_APPRAISE,S_INVITE_APPRAISE_ITEM,S_INVITE_COMM,S_INVITE_EXPERT,S_IN
VITE_ITEM,S_INVITE_PRODUCT_AFFIX,S_INVITE_SALER_AFFIX,S_JD_AREA,S_JD_REFERENCE_P
RICE,S_JD_SCHEME_MODEL,S_JD_SEND_ORG,S_JD_SEND_SCHEME,S_MESSAGE,S_PLAT_PRODUCT_6
,S_PRODUCT
通过这种方法，可以将输入结果的上限从VARCHAR2的SQL类型4000扩大到PL/SQL的类型的32767，而且由于只在最后输出的部分使用了LOB类型，对性能基本上没有什么影响。