Home » SQL & PL/SQL » SQL & PL/SQL » problem in creating user-defined aggregate function.
problem in creating user-defined aggregate function. [message #151792] Mon, 19 December 2005 04:39 Go to next message
ramabhupalrr
Messages: 69
Registered: July 2005
Location: India
Member

Hi all,

create or replace function toList(input varchar2) return varchar2
aggregate using toListImp1;

Here toListImp1 type has created successfully.but i have executed the above code at sqlplus promt. it showing, function created with comilation errors.

what could be the problem?

Any suggestions will be apreciated.


Thanks,
Bhupal.
Re: problem in creating user-defined aggregate function. [message #151965 is a reply to message #151792] Tue, 20 December 2005 02:21 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Are you trying to tease us with imaginary functionality?

I didn't know that you could write your own aggregate functions in PL/SQL, so I checked the 10gR2 doco, and STILL don't know how to do it!!!!

Quote:

function_declaration ::=
FUNCTION function_name
[(parameter_declaration [, parameter_declaration]...)]
RETURN datatype [ PIPELINED ] { IS | AS }


Are you making this up? Or is it an undocumented feature?

_____________
Ross Leishman
Re: problem in creating user-defined aggregate function. [message #151969 is a reply to message #151965] Tue, 20 December 2005 02:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
rleishman wrote on Tue, 20 December 2005 09:21

Are you making this up? Or is it an undocumented feature?
Nope, it is a documented feature:
excerpt from Oracle9i Data Cartridge Developer's Guide Release 2 (9.2) (Part Number A96595-01)


Example: Creating and Using a User-Defined Aggregate

This example illustrates creating a simple user-defined aggregate function SecondMax() that returns the second-largest value in a set of numbers.

Creating SecondMax()

  1. Implement the type SecondMaxImpl to contain the ODCIAggregate routines.
    create type SecondMaxImpl as object
    (
      max NUMBER, -- highest value seen so far 
    
      secmax NUMBER, -- second highest value seen so far
      static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl) 
        return number,
      member function ODCIAggregateIterate(self IN OUT SecondMaxImpl, 
        value IN number) return number,
      member function ODCIAggregateTerminate(self IN SecondMaxImpl, 
        returnValue OUT number, flags IN number) return number,
      member function ODCIAggregateMerge(self IN OUT SecondMaxImpl, 
        ctx2 IN SecondMaxImpl) return number
    
    );
    /
  2. Implement the type body for SecondMaxImpl.
    create or replace type body SecondMaxImpl is 
    static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl) 
    return number is 
    begin
      sctx := SecondMaxImpl(0, 0);
      return ODCIConst.Success;
    
    end;
    
    member function ODCIAggregateIterate(self IN OUT SecondMaxImpl, value IN number) 
    return number is
    begin
      if value > self.max then
        self.secmax := self.max;
        self.max := value;
      elsif value > self.secmax then
        self.secmax := value;
    
      end if;
      return ODCIConst.Success;
    end;
    
    member function ODCIAggregateTerminate(self IN SecondMaxImpl, returnValue OUT 
    number, flags IN number) return number is
    begin
      returnValue := self.secmax;
      return ODCIConst.Success;
    end;
    
    member function ODCIAggregateMerge(self IN OUT SecondMaxImpl, ctx2 IN 
    number is
    begin
      if ctx2.max > self.max then
    
        if ctx2.secmax > self.secmax then 
          self.secmax := ctx2.secmax;
        else
          self.secmax := self.max;
        end if;
        self.max := ctx2.max;
      elsif ctx2.max > self.secmax then
    
        self.secmax := ctx2.max;
      end if;
      return ODCIConst.Success;
    end;
    end;
    /
  3. Create the user-defined aggregate.
    CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER 
    PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;




Using SecondMax()

SELECT SecondMax(salary), department_id
  FROM employees
  GROUP BY department_id
 HAVING SecondMax(salary) > 9000;




I have even used user-defined aggregates in several production environments. Search the forum for CONCAT_ALL for another example.
Never assume anything, Ross Wink

Now, back to the original problem: without proper code sample we cannot help you I'm afraid. You said that the function was created with errors. What errors? Do you have an error message?

MHE

[edit: removed silly html code in quote]

[Updated on: Tue, 20 December 2005 02:46]

Report message to a moderator

Re: problem in creating user-defined aggregate function. [message #152019 is a reply to message #151965] Tue, 20 December 2005 09:28 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
It's definitely possible.

Here (towards the bottom of that page) there's a custom aggregate function to compute greatest common denominator (GCD), for example.
Re: problem in creating user-defined aggregate function. [message #152041 is a reply to message #151792] Tue, 20 December 2005 12:09 Go to previous messageGo to next message
ramabhupalrr
Messages: 69
Registered: July 2005
Location: India
Member

Hi all,

Thank you for help. I worked it.



Bhupal.
Re: problem in creating user-defined aggregate function. [message #202425 is a reply to message #151965] Thu, 09 November 2006 13:40 Go to previous message
toadster
Messages: 7
Registered: November 2006
Junior Member
UDAF (user defined aggreate functions) are possible.

Read oracle documentaion at
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96595/dci11agg.htm#1004572

Mukarram
Previous Topic: Error ORA-06512
Next Topic: executeQuery vs. executeUpdate on a resultset
Goto Forum:
  


Current Time: Sun Dec 04 08:45:48 CST 2016

Total time taken to generate the page: 0.19187 seconds