Limit characters returned in oracle sql query

Question

I need to limit this field to 4000 characters. How to limit it to agg list? Thank you

LISTAGG(ORDER_IMPRESSION.IMPRESSION, ',') WITHIN GROUP (ORDER BY ORDER_IMPRESSION.LINE) 

Answer 1

With only a little effort. Something like that:

select listagg((case when running_len < 4000 then oi.impression end), ',') within group (order by oi.line)
from (select oi.*,
             sum(length(oi.impression) + 1) over (partition by ?? order by oi.line) as running_len
      from order_impression oi
     ) oi
group by ??;

This calculates the execution length and only aggregates values ​​that do not exceed the length. ??- this is what you use for aggregation. This assumes that it lineis the only one, therefore it order byis stable.

This will not include impressionthat exceeds the length, and there will be nothing after that. This does not remove the impression. This logic is possible, but it complicates the request.

Answer 2

( SUM(...) OVER (...)), 4000 :

SELECT LISTAGG(
         CASE
           WHEN prev_len >= 4000 THEN NULL
           WHEN prev_len + len <= 4000 THEN value
           ELSE SUBSTR( value, 1, 4000 - prev_len )
         END
       ) WITHIN GROUP ( ORDER BY line ) AS value
FROM   (
  SELECT impression,
         line,
         LENGTH( impression ) AS len,
         COALESCE(
           SUM( 1 + LENGTH( impression ) )
             OVER ( ORDER BY line ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ),
           0
         ) AS prev_len
  FROM   order_impression
);

Answer 3

You can write a custom aggregation function for aggregation VARCHAR2in 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 DBMS_LOB.SUBSTR( CLOBAGG( IMPRESSION ), 4000 )
FROM   (
  SELECT   IMPRESSION
  FROM     ORDER_IMPRESSION
  ORDER BY line
)

Answer 4

If you are an early adopter of 12.2, you can use the ON OVERFLOW TRUNCATE clause ...

ON OVERFLOW TRUNCATE