....
Is it possible to create a user-defined aggregate function with more than 1 argument?
....Though user define aggregate function doesn't support more than one argument, it can accept object types with multiple attributes.
so instead of more than 1 argument, you could have an object type defined with multiple attributes like this, and have the passed to the user defined aggregate functions.
So check if that helps.
demo@ORA11G> create or replace type myobj is object(x varchar2(30),y varchar2(1));
2 /
Type created.
demo@ORA11G> create or replace type vcarray is table of myobj;
2 /
Type created.
demo@ORA11G>
demo@ORA11G> create or replace type stragg_agg_type as object
2 (
3 l_total vcarray,
4
5 static function odciaggregateinitialize(ctx in out stragg_agg_type) return number,
6
7 member function odciaggregateiterate(self in out stragg_agg_type,
8 val myobj) return number,
9
10 member function odciaggregatemerge(self in out stragg_agg_type,
11 ctx2 stragg_agg_type) return number,
12
13 member function odciaggregateterminate(self in stragg_agg_type,
14 returnvalue out varchar2,
15 flags in number) return number
16 );
17 /
Type created.
demo@ORA11G> create or replace type body stragg_agg_type is
2 static function odciaggregateinitialize(ctx in out stragg_agg_type)
3 return number is
4 begin
5 ctx := stragg_agg_type( vcarray() );
6 return ODCIConst.Success;
7 end;
8
9 member function odciaggregateiterate(self in out stragg_agg_type,
10 val myobj) return number is
11 begin
12 if val is not null then
13 self.l_total.extend;
14 self.l_total(self.l_total.count) := val;
15 end if;
16 return ODCIConst.Success;
17 end;
18
19 member function odciaggregatemerge(self in out stragg_agg_type,
20 ctx2 stragg_agg_type)
21 return number as
22 begin
23 self.l_total := ctx2.l_total;
24 return odciconst.success;
25 end;
26
27 member function odciaggregateterminate(self in stragg_agg_type,
28 returnvalue out varchar2,
29 flags in number)
30 return number as
31 l_value varchar2(1);
32 begin
33 for x in (select distinct x,y from table(self.l_total) order by 1)
34 loop
35 returnvalue := returnvalue ||x.y||x.x;
36 l_value := x.y ;
37 end loop;
38 returnvalue := trim(l_value from returnvalue);
39 return odciconst.success;
40 end;
41 end;
42 /
Type body created.
demo@ORA11G> create or replace function stragg(x myobj)
2 return varchar2
3 aggregate using stragg_agg_type;
4 /
Function created.
demo@ORA11G> column enames format a40
demo@ORA11G> select deptno,stragg(myobj(ename,',')) as enames
2 from emp
3 group by deptno;
DEPTNO ENAMES
---------- ----------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
demo@ORA11G>
demo@ORA11G> select deptno,ename,stragg(myobj(ename,'|'))
2 over( partition by deptno order by ename ) as enames
3 from emp
4 order by deptno,ename
5 /
DEPTNO ENAME ENAMES
---------- ---------- ----------------------------------------
10 CLARK CLARK
10 KING CLARK|KING
10 MILLER CLARK|KING|MILLER
20 ADAMS ADAMS
20 FORD ADAMS|FORD
20 JONES ADAMS|FORD|JONES
20 SCOTT ADAMS|FORD|JONES|SCOTT
20 SMITH ADAMS|FORD|JONES|SCOTT|SMITH
30 ALLEN ALLEN
30 BLAKE ALLEN|BLAKE
30 JAMES ALLEN|BLAKE|JAMES
30 MARTIN ALLEN|BLAKE|JAMES|MARTIN
30 TURNER ALLEN|BLAKE|JAMES|MARTIN|TURNER
30 WARD ALLEN|BLAKE|JAMES|MARTIN|TURNER|WARD
14 rows selected.
demo@ORA11G>