2 Replies Latest reply: Feb 1, 2006 12:27 PM by 458726 RSS

    Aggregate function to bring values in list form

    458726
      Hi,
      I do not have familiarity with aggregate functions, therefore I ask for of help.
      Below type follows the code of the one creation type object, type object body and of the aggregate function to bring the content of one 'select' in list form.
      The function below functions perfectly, but I need that two parameters and not only one are passed, as it is currently.

      One is the column and the other is to caracter delimiter, that would be the criterion of the user.
      The delimiter standard would be a delimiter that would be attributed, case the user informed only the first parameter.

      Example:
      select list_function(column,';') from table.
      Resulted:
      value_1;value_2;value_n

      In case that ' # ' were defined as delimiter standard caracter, then the command below would be thus:
      select list_function(column_1) from table

      Resulted:
      valor_1#valor_2#valor_n


      Creation type object, type object body and of the aggregate function

      CREATE OR REPLACE TYPE t_list AS OBJECT
      (
      g_string VARCHAR2(32767),

      STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_list)
      RETURN NUMBER,

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

      MEMBER FUNCTION ODCIAggregateTerminate(self IN t_list,
      returnValue OUT VARCHAR2,
      flags IN NUMBER)
      RETURN NUMBER,

      MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_list,
      ctx2 IN t_list)
      RETURN NUMBER
      );
      -- ---------------------------------------------------------------------
      CREATE OR REPLACE TYPE BODY t_list IS
      STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_list)
      RETURN NUMBER IS
      BEGIN
      sctx := t_list(NULL);
      RETURN ODCIConst.Success;
      END;

      MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_list,
      value IN VARCHAR2 )
      RETURN NUMBER IS
      BEGIN
      SELF.g_string := self.g_string || ',' || value;
      RETURN ODCIConst.Success;
      END;

      MEMBER FUNCTION ODCIAggregateTerminate(self IN t_list,
      returnValue OUT VARCHAR2,
      flags IN NUMBER)
      RETURN NUMBER IS
      BEGIN
      returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
      RETURN ODCIConst.Success;
      END;

      MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_list,
      ctx2 IN t_list)
      RETURN NUMBER IS
      BEGIN
      SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
      RETURN ODCIConst.Success;
      END;
      END;
      -- ---------------------------------------------------------------------
      CREATE OR REPLACE FUNCTION dbf_list (p_input VARCHAR2)
      RETURN VARCHAR2
      PARALLEL_ENABLE AGGREGATE USING t_list;
        • 1. Re: Aggregate function to bring values in list form
          Jens Petersen
          Padders supplied code for this on AskTom here
          http://asktom.oracle.com/pls/ask/f?p=4950:8:12609538456763681460::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2196162600402#23978109789638
          • 2. Re: Aggregate function to bring values in list form
            458726
            Ok, thanks, it is almost this that I need.
            The example that has in this link of the AskTom functions perfectly.
            But of the skill that is, the call of the function is:

            SQL> SELECT concat_all (concat_expr (deptno, '|')) deptnos,
            2 concat_all (concat_expr (dname, ',')) dnames
            3 FROM dept;

            And I need that he is thus:
            SQL> SELECT concat_all (deptno, '|') deptnos,
            2 concat_all (dname, ',') dnames
            3 FROM dept;

            Try to modify, but the aggregate functions accept only one parameter.
            It has some skill to modify this?

            It will be that somebody can help me? Please...

            Message was edited by:
            arf