6 Replies Latest reply: Sep 26, 2012 4:46 AM by BluShadow RSS

    Writing user defined function the same way as the oracle functions

    960593
      Hi Guys,

      In one of the interviews , I have attended the guy asked me to write a user defined function which will take the column name and list all the values .

      For example

      Table Name:Employees
      Column Name: Employee_Name

      Employee_name
      Scott
      Ivgun
      Jack
      Shane

      The query should be in this fromat

      SELECT col_agg(Employee_name) emp_name from Employees;

      The output shoulld be

      Emp_names
      scott,ivgun,jack,shane

      Please let me know if this is possible


      Any suggestions will be highly appreciated.

      Thanks,
      Ranjan
        • 1. Re: Writing user defined function the same way as the oracle functions
          Purvesh K
          You can tell the Ignorant Guy that if (S)he is on 11g, you can use LISTAGG as below:
          SELECT LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
            FROM employees;
          Or 9i or above but not 11g then
          SELECT deptno,
                 LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
                 KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
          FROM   (SELECT deptno,
                         ename,
                         ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
                         ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
                  FROM   emp)
          GROUP BY deptno
          CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
          START WITH curr = 1;
          you can find more String Aggregation techniques on Oracle - Base - String Aggregation. Why must one re-invent the wheel?
          Something to think about, right? (Obviously not you in this situation, the Inverviewer) :)
          • 3. Re: Writing user defined function the same way as the oracle functions
            960593
            I am aware of this function.But my requirement is different

            can an user defined function be written in the same way let rename it listagg_udf


            Regards,
            Ranjan
            • 4. Re: Writing user defined function the same way as the oracle functions
              N_i_R_v_A_n_A
              You can use XMLAgg as well

              SELECT XMLAGG(XMLELEMENT(E,ename||',')).EXTRACT('//text()') "Result"
              FROM emp;
              • 5. Re: Writing user defined function the same way as the oracle functions
                Purvesh K
                957590 wrote:
                I am aware of this function.But my requirement is different

                can an user defined function be written in the same way let rename it listagg_udf
                It looks like you did not read the Link in my earlier post.

                here is it again; Click Here. Using Collect Function of 10g it can be achieved and you need to write a UDF.
                • 6. Re: Writing user defined function the same way as the oracle functions
                  BluShadow
                  You could certainly write a user defined aggregate function if you like...

                  e.g. this function will aggregate strings into a CLOB...
                  create or replace type clobagg_type as object
                    (
                    text clob,
                    static function ODCIAggregateInitialize(sctx in out clobagg_type) return number,
                    member function ODCIAggregateIterate(self in out clobagg_type, value in clob) return number,
                    member function ODCIAggregateTerminate(self in clobagg_type, returnvalue out clob, flags in number) return number,
                    member function ODCIAggregateMerge(self in out clobagg_type, ctx2 in clobagg_type) return number
                    );
                  / 
                  create or replace type body clobagg_type is
                    static function ODCIAggregateInitialize(sctx in out clobagg_type) return number is
                    begin
                      sctx := clobagg_type(null) ;
                      return ODCIConst.Success ;
                    end;
                    member function ODCIAggregateIterate(self in out clobagg_type, value in clob) return number is
                    begin
                      self.text := self.text || value ;
                      return ODCIConst.Success;
                    end;
                    member function ODCIAggregateTerminate(self in clobagg_type, returnvalue out clob, flags in number) return number is
                    begin
                      returnValue := self.text;
                      return ODCIConst.Success;
                    end;
                    member function ODCIAggregateMerge(self in out clobagg_type, ctx2 in clobagg_type) return number is
                    begin
                      self.text := self.text || ctx2.text;
                      return ODCIConst.Success;
                    end;
                  end;
                  / 
                  create or replace function clobagg(input clob) return clob
                    deterministic
                    parallel_enable
                    aggregate using clobagg_type;
                  / 
                  
                  SQL> select trim(',' from clobagg(ename||',')) as enames from emp;
                  
                  ENAMES
                  -----------------------------------------------------------------------------------
                  SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
                  
                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  with t as
                    2    (select 'PFL' c1, 0 c2,110 c3 from dual union all
                    3     select 'LHL', 0 ,111 from dual union all
                    4     select 'PHL', 1, 111 from dual union all
                    5     select 'CHL', 2, 111 from dual union all
                    6     select 'DHL', 0, 112 from dual union all
                    7     select 'VHL', 1, 112 from dual union all
                    8     select 'CPHL', 0, 114 from dual union all
                    9     select 'WDCL', 1, 114 from dual union all
                   10     select 'AHL' ,2 ,114 from dual union all
                   11     select 'NFDL', 3, 114 from dual)
                   12  --
                   13  -- end of test data
                   14  --
                   15  select trim(clobagg(c1||' ')) as c1, c3
                   16  from (select * from t order by c3, c2)
                   17  group by c3
                   18* order by c3
                  SQL> /
                  
                  C1                                     C3
                  ------------------------------ ----------
                  PFL                                   110
                  LHL CHL PHL                           111
                  DHL VHL                               112
                  CPHL AHL NFDL WDCL                    114
                  Ok, it's more than just a function as it uses an object type linking into the internals of the ODCI... but it does what you ask. :)