6 Replies Latest reply on Sep 26, 2012 9:46 AM by BluShadow

# Writing user defined function the same way as the oracle functions

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
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
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) :)
• ###### 2. Re: Writing user defined function the same way as the oracle functions

In 11g you can use LISTAGG

• ###### 3. Re: Writing user defined function the same way as the oracle functions
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
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
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
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
-----------------------------------------------------------------------------------

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. :)