CODEWITHSUNDEEP
sqloracle-database
smattiko
smattiko

Reputation: 41

Limit characters returned in oracle sql query

I need to limit this field to 4000 characters. How do I limit it on the list agg? Thanks. 

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

Upvotes: 4

Views: 3863

Answers (4)

unleashed
unleashed

Reputation: 771

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

ON OVERFLOW TRUNCATE

Upvotes: 0

MT0
MT0

Reputation: 168681

You could calculate a running total of the string length (using the SUM(...) OVER (...) analytic function) and then use this to truncate the string to 4000 characters:

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

Upvotes: 2

MT0
MT0

Reputation: 168681

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 DBMS_LOB.SUBSTR( CLOBAGG( IMPRESSION ), 4000 )
FROM   (
  SELECT   IMPRESSION
  FROM     ORDER_IMPRESSION
  ORDER BY line
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Only with a bit of effort. Something like this:

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 running length and only aggregates values that do not exceed the length. The ?? is whatever you are using for aggregation. This does assume that line is unique, so the order by is stable.

This will not include the impression that exceeds the length -- and nothing after that. It does not cut the impression off. That logic is possible, but it does complicate the query.

Upvotes: 2

Related Questions