CREATE OR REPLACE TYPE strsum_t AS OBJECT (

  ls_sum                           VARCHAR2( 32767 )

, MEMBER FUNCTION get_separator
  RETURN VARCHAR2

, STATIC FUNCTION ODCIAggregateInitialize (
    ctx                            IN OUT strsum_t
  )
  RETURN NUMBER

, MEMBER FUNCTION ODCIAggregateIterate (
    self                           IN OUT strsum_t
  , value                          IN VARCHAR2
  )
  RETURN NUMBER

, MEMBER FUNCTION ODCIAggregateMerge (
    self                           IN OUT strsum_t
  , ctx                            IN strsum_t
  )
  RETURN NUMBER

, MEMBER FUNCTION ODCIAggregateTerminate (
    self                           IN strsum_t
  , value                          OUT VARCHAR2
  , flags                          IN NUMBER
  )
  RETURN NUMBER

);
/

CREATE OR REPLACE TYPE BODY strsum_t
AS

  MEMBER FUNCTION get_separator
  RETURN VARCHAR2
  IS
    ls_separator                   CONSTANT CHAR( 1 ) := ',';
  BEGIN
    RETURN ls_separator;
  END get_separator;

  STATIC FUNCTION ODCIAggregateInitialize (
    ctx                            IN OUT strsum_t
  )
  RETURN NUMBER
  IS
  BEGIN
    ctx := strsum_t(NULL);
    RETURN ODCIConst.Success;
  END ODCIAggregateInitialize;

  MEMBER FUNCTION ODCIAggregateIterate (
    self                           IN OUT strsum_t
  , value                          IN VARCHAR2
  )
  RETURN NUMBER
  IS
  BEGIN
    self.ls_sum := self.ls_sum || self.get_separator || value;
    RETURN ODCIConst.Success;
  END ODCIAggregateIterate;

  MEMBER FUNCTION ODCIAggregateMerge (
    self                           IN OUT strsum_t
  , ctx                            IN strsum_t
  )
  RETURN NUMBER
  IS
  BEGIN
    self.ls_sum := LTRIM(self.ls_sum, self.get_separator)
                || self.get_separator
                || LTRIM(ctx.ls_sum, self.get_separator)
    ;
    RETURN ODCIConst.Success;
  END ODCIAggregateMerge;

  MEMBER FUNCTION ODCIAggregateTerminate (
    self                           IN strsum_t
  , value                          OUT VARCHAR2
  , flags                          IN NUMBER
  )
  RETURN NUMBER
  IS
  BEGIN
    value := LTRIM(self.ls_sum, self.get_separator);
    RETURN ODCIConst.Success;
  END ODCIAggregateTerminate;

END;
/

CREATE OR REPLACE FUNCTION strsum (
  as_str_i                        VARCHAR2
)
RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE USING
strsum_t;
/
COLUMN pencil_colors FORMAT A30
SELECT box_number
     , STRSUM(pencil_color) pencil_colors
  FROM pencil_box
 GROUP BY
       box_number
/
BOX_NUMBER PENCIL_COLORS
 ---------- ------------------------------
 
          1  Green,Yellow,Blue
          2  Red,Orange,Cyan
          3  Pink,Tan
          4  Black,Turquoise,White,Gray
 --