字符串连接超长的解决（一）

from  http://blog.itpub.net/4227/viewspace-588644/

2009-04-10 23:25:15

分类： 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类型，对性能基本上没有什么影响。