Oracle User-defined aggretate function - Return Value with error ORA-06502

Question

I tried to define a user-defined aggregate function which takes a numeric value and returns a value of type VARCHAR2. It works perfectly, as long as the return value does not exceed 40 characters. However, I did not find any references to restictions in length, so I am a bit confused. And 41 characters are not sooo much, so why should this be a problem?

To try it our yourself, here is the complete test code. As written, it produces error ORA-06502. If you remove the x in the return value, it has no problem.

Any ideas? I would like to return more than 40 characters.

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;
/

Answer 1

In the final function, you are not returning a NUMBER it needs to be VARCHAR2 (otherwise Oracle will try to implicitly cast the returned value back to a number and a NUMBER data-type has a maximum precision of 38-40 digits).

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

fiddle