字串連線超長的解決（一）

在我的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型別，對效能基本上沒有什麼影響。