Using Oracle’s ListAgg with distinct

Oracle’s 11g ListAgg function is a great way to concat multiple rows into a single column. However, it has a major limitation (in addition to the light documentation) – you can’t use distinct in ListAgg, a serious limitation. Online solutions suggest subqueries or regular expressions. I decided to write my own aggregate function to replace ListAgg.
Here goes:

  • First of all, I created the type specification:
    create or replace type TextAggregation as object
    (
      aggString VARCHAR2(32767), 
      static function ODCIAggregateInitialize(sctx IN OUT TextAggregation) 
        return number,
      member function ODCIAggregateIterate(self IN OUT TextAggregation, 
        value IN VARCHAR2) return number,
      member function ODCIAggregateTerminate(self IN TextAggregation, 
        returnValue OUT VARCHAR2, flags IN VARCHAR2) return number,
      member function ODCIAggregateMerge(self IN OUT TextAggregation, 
        ctx2 IN TextAggregation) return number
    );
    /
    
  • Then, the type body:
    create or replace type body TextAggregation is 
    static function ODCIAggregateInitialize(sctx IN OUT TextAggregation) 
    return number is 
    begin
      sctx := TextAggregation('');
      return ODCIConst.Success;
    end;
    
    member function ODCIAggregateIterate(self IN OUT TextAggregation, value IN VARCHAR2) return number is
      location number;
    begin
    	location := instr(',' || aggString || ',' , ',' || value || ',');
    	
    	if location > 0 then
    		return ODCIConst.Success;
    	end if;
    	
      if (aggString is null) then
        aggString := value;
      else
        aggString := aggString || ',' || value;
      end if;
      
    	return ODCIConst.Success;
    end;
    
    member function ODCIAggregateTerminate(self IN TextAggregation, 
        returnValue OUT VARCHAR2, flags IN VARCHAR2) return number is
    begin
      returnValue := self.aggString;
      return ODCIConst.Success;
    end;
    
    member function ODCIAggregateMerge(self IN OUT TextAggregation, ctx2 IN TextAggregation) return number is
    begin
      self.aggString := ctx2.aggString;
      return ODCIConst.Success;
    end;
    end;
    /
    
  • And then the actual function
    CREATE or replace FUNCTION MyListAgg (input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING TextAggregation;
    /