对于按照自定义聚集函数连接起来的字符串中的数据不能自动按照一定的规则进行组合解决办法是在类型体中对数据进行排序处理
下边是修改后的代码
CREATE OR REPLACE TYPE VcArrayType IS TABLE OF VARCHAR();
/
CREATE OR REPLACE TYPE STR_CONN_TYPE AS OBJECT
(
vStr VARCHAR()
vStr VcArrayType
STATIC FUNCTION
ODCIAggregateInitialize(sctx IN OUT STR_CONN_TYPE )
RETURN NUMBER
MEMBER FUNCTION
ODCIAggregateIterate(SELF IN OUT STR_CONN_TYPE
VALUE IN NUMBER )
RETURN NUMBER
MEMBER FUNCTION
ODCIAggregateMerge(SELF IN OUT STR_CONN_TYPE
ctx IN STR_CONN_TYPE)
RETURN NUMBER
MEMBER FUNCTION
ODCIAggregateTerminate(SELF IN STR_CONN_TYPE
returnValue OUT VARCHAR
flags IN NUMBER)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY STR_CONN_TYPE
IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT STR_CONN_TYPE)
RETURN NUMBER
IS
BEGIN
sctx := STR_CONN_TYPE(NULL);
sctx := STR_CONN_TYPE(VcArrayType());
RETURN ODCIConstSuccess;
END;
MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT STR_CONN_TYPE
VALUE IN NUMBER )
RETURN NUMBER
IS
BEGIN
SELFvStr := SELFvStr || ; || VALUE;
vStrEXTEND;
vStr(vStrCOUNT) := VALUE;
RETURN ODCIConstSuccess;
END;
MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT STR_CONN_TYPE
ctx IN STR_CONN_TYPE)
RETURN NUMBER
IS
BEGIN
RETURN ODCIConstSuccess;
END;
MEMBER FUNCTION ODCIAggregateTerminate(SELF IN STR_CONN_TYPE
returnValue OUT VARCHAR
flags IN NUMBER)
RETURN NUMBER
IS
tmp_vStr VARCHAR();
BEGIN
returnValue := LTRIM(SELFvStr;);
FOR rec_Value IN (SELECT column_value FROM TABLE(vStr) ORDER BY to_number(column_value)) LOOPtmp_vStr := tmp_vStr || ; || lumn_value;
END LOOP;
returnValue := LTRIM(tmp_vStr;);
RETURN ODCIConstSuccess;
END;
END;
/
CREATE OR REPLACE FUNCTION ConnStr(input VARCHAR)
RETURN VARCHAR
PARALLEL_ENABLE AGGREGATE USING STR_CONN_TYPE;
/
