Convert column values to comma separated values as CLOB without using XMLAGG

Question

I am using Oracle 12.1. I have an ID column on which I am using group by and want to convert values in another column (say NAME) to comma separated string as a CLOB (not VARCHAR2 because of its limitation to 4000 chars).

I tried with LISTAGG function, but it fails as the comma separated string is more than 4000 chars. (There is an improved version of LISTAGG to restrict the overflow, but is not available in Oracle 12.1)

With XMLAGG, it works, but I don't want to use XMLAGG because this particular function is called every 5 seconds and is giving performance issues sometimes and also once in a while "ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT"

What I would like to have is:

1. Either need a way to convert column values to comma separated string as a CLOB (without using LISTAGG, XMLAGG)

OR

2. I am fine with skipping some column values and use "..." to inform that there are more values. (lets say we can consider only 5 rows instead of all rows for given ID (group by column))

Answer 1

From my answer here, you can write a custom aggregation function to aggregate VARCHAR2s into a CLOB:

CREATE OR REPLACE TYPE CLOBAggregation AS OBJECT(
  value CLOB,

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

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

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT CLOBAggregation,
    returnValue    OUT CLOB,
    flags       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT CLOBAggregation,
    ctx         IN OUT CLOBAggregation
  ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY CLOBAggregation
IS
  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT CLOBAggregation
  ) RETURN NUMBER
  IS
  BEGIN
    ctx := CLOBAggregation( NULL );
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT CLOBAggregation,
    value       IN     VARCHAR2
  ) RETURN NUMBER
  IS
  BEGIN
    IF value IS NULL THEN
      NULL;
    ELSIF self.value IS NULL THEN
      self.value := value;
    ELSE
      self.value := self.value || ',' || value;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT CLOBAggregation,
    returnValue    OUT CLOB,
    flags       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    returnValue := self.value;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT CLOBAggregation,
    ctx         IN OUT CLOBAggregation
  ) RETURN NUMBER
  IS
  BEGIN
    IF self.value IS NULL THEN
      self.value := ctx.value;
    ELSIF ctx.value IS NULL THEN
      NULL;
    ELSE
      self.value := self.value || ',' || ctx.value;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;
END;
/

CREATE FUNCTION CLOBAgg( value VARCHAR2 )
RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING CLOBAggregation;
/

Then you can do:

SELECT id,
       CLOBAGG( name ) AS names
FROM   (
  SELECT   id,
           name
  FROM     your_table
  ORDER BY your_ordering_column
)
GROUP BY id;

---

OR

I am fine with skipping some column values and use "..." to inform that there are more values. (lets say we can consider only 5 rows instead of all rows for given ID (group by column))

SELECT id,
       LISTAGG(
         CASE rn WHEN 6 THEN '...' ELSE name END,
         ','
       ) WITHIN GROUP (ORDER BY rn) AS names
FROM   (
  SELECT id,
         name,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY your_ordering_column) AS rn
  FROM   your_table
)
WHERE  rn <= 6
GROUP BY id;

Answer 2

The answer here does not suit my circumstances because I need to limit it to a single SELECT query without relying on additional objects created in the database.

Lots of other folks are suggesting going to XMLAGG to get around the 4000 character limit. This does work, but then the problem is that certain special characters get "escaped" by XMLAGG, e.g., < becomes <, & becomes &, etc.

I found the solution to get past this here: https://forums.oracle.com/ords/apexds/post/xmlagg-apostrophe-and-quotes-issue-2107

If you wrap the XMLAGG with XMLCAST casting it back to a CLOB, all of those encodings get "unescaped".

I'm posting this for the greater good, in case someone (like me) stumbles along here facing my limitation of not creating additional objects to solve the problem.