Oracle自定义聚合函数返回值遇ORA-06502错误：超长返回值受限

问题现象 #

定义了接收数值类型参数、返回VARCHAR2类型的Oracle自定义聚合函数，当返回值长度不超过40字符时可正常运行，但返回值达到41字符时触发ORA-06502错误。测试代码如下：

create or replace TYPE TEST_OBJ AS OBJECT
( -- Attribute
  ID       NUMBER, -- Eindeutige ID für jeden Aufruf
  -- Funktionen
  STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT TEST_OBJ)
     return number,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT TEST_OBJ, VALUE IN NUMBER)
     return number,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT TEST_OBJ, ctx2 IN TEST_OBJ)
     return number,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN TEST_OBJ, ReturnValue OUT VARCHAR2, flags IN NUMBER)
     return number
);
/

create or replace type body TEST_OBJ is
STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT TEST_OBJ)
     return number is
BEGIN
  return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate(self IN OUT TEST_OBJ, VALUE IN NUMBER)
     return number is
BEGIN
  return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT TEST_OBJ, ctx2 IN TEST_OBJ)
     return number is
BEGIN
  return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate(self IN TEST_OBJ, ReturnValue OUT VARCHAR2, flags IN NUMBER)
     return number is
BEGIN
  ReturnValue :=  '0123456789012345678901234567890123456789x';
  return ODCIConst.Success;
END;
END;
/

create or replace FUNCTION TEST(input NUMBER) return NUMBER PARALLEL_ENABLE
   AGGREGATE USING TEST_OBJ;
/

错误原因 #

1. 函数返回类型不匹配：自定义聚合函数TEST的返回类型被错误定义为NUMBER，但类型体TEST_OBJ的ODCIAggregateTerminate方法返回的是VARCHAR2。Oracle会尝试将字符串隐式转换为数值类型，而数值类型的字符表示最大长度约为40（对应NUMBER的最大精度），超过这个长度就会触发ORA-06502数值或值错误。
2. 未显式指定VARCHAR2长度：即使修正返回类型，若未指定VARCHAR2的长度，Oracle会使用默认长度（通常为1或取决于上下文），仍可能导致截断或报错。

修正方案 #

步骤1：修改聚合函数的返回类型 #

将函数TEST的返回类型改为VARCHAR2，并显式指定足够的长度（比如VARCHAR2(4000)，支持最大4000字符的返回值）：

create or replace FUNCTION TEST(input NUMBER) return VARCHAR2(4000) PARALLEL_ENABLE
   AGGREGATE USING TEST_OBJ;
/

步骤2：确认类型体的返回值处理 #

确保ODCIAggregateTerminate方法中返回的字符串长度不超过函数定义的VARCHAR2长度限制。若需要更长的返回值，可使用CLOB类型（需同时修改类型体的ReturnValue参数类型和函数返回类型为CLOB）。

完整修正后代码 #

create or replace TYPE TEST_OBJ AS OBJECT
( -- Attribute
  ID       NUMBER, -- Eindeutige ID für jeden Aufruf
  -- Funktionen
  STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT TEST_OBJ)
     return number,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT TEST_OBJ, VALUE IN NUMBER)
     return number,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT TEST_OBJ, ctx2 IN TEST_OBJ)
     return number,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN TEST_OBJ, ReturnValue OUT VARCHAR2, flags IN NUMBER)
     return number
);
/

create or replace type body TEST_OBJ is
STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT TEST_OBJ)
     return number is
BEGIN
  return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate(self IN OUT TEST_OBJ, VALUE IN NUMBER)
     return number is
BEGIN
  return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT TEST_OBJ, ctx2 IN TEST_OBJ)
     return number is
BEGIN
  return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate(self IN TEST_OBJ, ReturnValue OUT VARCHAR2, flags IN NUMBER)
     return number is
BEGIN
  ReturnValue :=  '0123456789012345678901234567890123456789x';
  return ODCIConst.Success;
END;
END;
/

create or replace FUNCTION TEST(input NUMBER) return VARCHAR2(4000) PARALLEL_ENABLE
   AGGREGATE USING TEST_OBJ;
/

验证 #

执行聚合函数调用，比如：

SELECT TEST(1) FROM DUAL;

此时返回41字符的字符串将正常执行，不会触发ORA-06502错误。

内容的提问来源于stack exchange，提问作者Thorsten