Oracle User-Defined Aggregate Functions Interface (Convert into a Single String)

This section describes the routines that need to be implemented to define a user-defined aggregate function. The routines are implemented as methods in an object type. Then the CREATE FUNCTION statement is used to actually create the aggregate function

ODCIAggregateDelete()	Removes an input value from the current group.
ODCIAggregateInitialize()	Initializes the aggregation context and instance of the implementation object type, and returns it as an OUT parameter.
ODCIAggregateIterate()	Iterates through input rows by processing the input values, updating and then returning the aggregation context.
ODCIAggregateMerge()	Merges two aggregation contexts into a single object instance during either serial or parallel evaluation of the user-defined aggregate.
ODCIAggregateTerminate()	Calculates the result of the aggregate computation and performs all necessary cleanup, such as freeing memory.
ODCIAggregateWrapContext()	Integrates all external pieces of the current aggregation context to make the context self-contained.

---First define the Object type

CREATE OR REPLACE TYPE string_agg_obj_typ AS OBJECT(
   g_string VARCHAR2(32767)

, STATIC FUNCTION odciaggregateinitialize( 
  sctx IN OUT string_agg_obj_typ) RETURN NUMBER
 
, MEMBER FUNCTION odciaggregateiterate(
  self  IN OUT string_agg_obj_typ
, VALUE     IN VARCHAR2)RETURN NUMBER
 
, MEMBER FUNCTION odciaggregateterminate(
  self        IN     string_agg_obj_typ
, returnvalue OUT    VARCHAR2
, flags       IN     NUMBER) RETURN NUMBER
 
, MEMBER FUNCTION odciaggregatemerge(
  self        IN OUT string_agg_obj_typ
, ctx2        IN     string_agg_obj_typ) RETURN NUMBER);
/
SHOW ERRORS

---First define the Object type body 
CREATE OR REPLACE TYPE BODY string_agg_obj_typ
IS
   STATIC FUNCTION odciaggregateinitialize( 
      sctx IN OUT string_agg_obj_typ)
      RETURN NUMBER
   IS
   BEGIN
      sctx    := string_agg_obj_typ( NULL);
      RETURN 0;
   END;

   MEMBER FUNCTION odciaggregateiterate(
  self         IN OUT string_agg_obj_typ
, VALUE        IN     VARCHAR2)
      RETURN NUMBER
   IS
   BEGIN
      self.g_string :=
            self.g_string
         || ','
         || VALUE;
      RETURN 0;
   END;

   MEMBER FUNCTION odciaggregateterminate(
  self          IN  string_agg_obj_typ
, returnvalue  OUT  VARCHAR2
, flags         IN  NUMBER)
      RETURN NUMBER
   IS
   BEGIN
      returnvalue   :=
         RTRIM(LTRIM(self.g_string
                   , ',')
             , ',');
      RETURN 0;
   END;

   MEMBER FUNCTION odciaggregatemerge(
  self          IN OUT string_agg_obj_typ
, ctx2          IN     string_agg_obj_typ)
      RETURN NUMBER
   IS
   BEGIN
      self.g_string  :=
            self.g_string
         || ','
         || ctx2.g_string;
      RETURN 0;
   END;
END;
/

SHOW ERRORS

---- Create a function which will accept the string a input 
---- parameter and return a concatenated String

CREATE OR REPLACE FUNCTION string_agg_fnc( p_input VARCHAR2)
   RETURN VARCHAR2
   PARALLEL_ENABLE
   AGGREGATE USING string_agg_obj_typ;
/

SHOW ERRORS

--Now run the SQL as below  
 SQL>   SELECT organization_id
  2         , string_agg_fnc( segment1)
  3      FROM mtl_system_items_b
  4     WHERE organization_id = 103
  5       AND ROWNUM <= 10
  6  GROUP BY organization_id;

ORGANIZATION_ID     STRING_AGG_FNC(SEGMENT1)
---------------    ----------------------------------------
            103     01R0593,06P5760,06P5759,06P5755,06P5323
SQL>



------------------------------------------------------------------
SQL to Convert ROWS into Concatenated string
-----------------------------------------------------------------
SQL> SELECT organization_id,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(segment1,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS items
FROM   (SELECT organization_id,
               segment1,
 ROW_NUMBER() OVER (PARTITION BY organization_id ORDER BY segment1) AS curr,
 ROW_NUMBER() OVER (PARTITION BY organization_id ORDER BY segment1) -1 AS prev
 FROM   mtl_system_items_b where organization_id = 103 and rownum <= 5)
GROUP BY organization_id
CONNECT BY prev = PRIOR curr 
      AND organization_id = PRIOR organization_id
START WITH curr = 1

ORGANIZATION_ID    ITEMS
---------------   ------------------------------------------
            103   01R0593,06P5323,06P5755,06P5759,06P5760
           
SQL>