User Defined Operators
Going through Oracle documentation (yes, I do that sometimes), I found the CREATE OPERATOR command. This command allows you to create user-defined operators. To quote psoug reference:
Oracle allows developers of object-oriented applications to extend the list of built-in relational operators (for example, +, -, /, *, LIKE, AND, OR) with domain specific operators (for example, Contains, Within_Distance, Similar) called user-defined operators. A user-defined operator can be used anywhere built-in operators can be used, for example, in the select list or the where clause. Similar to built-in operators, a user-defined operator may support arguments of different types, and that it may be evaluated using an index. Similar to built-in operators, user-defined operators allow efficient content-based querying and sorting on object data.
I have never created a user-defined operator before, so being the “always-trying-something-new” type of guy, I quickly put together a simple test. Here it is: (more…)
Filed in Oracle with Comments Off | Tags: operator, user-definedUser-Defined Aggregate Functions
I had a requirement to write a query that should return a concatenation of column values from different rows and grouped by another column. Well, that would be easy if Oracle provided an aggregate function similar to SUM or COUNT for example, but instead of summing or counting, it would concatenate. Alas! There is no such pre-defined function in Oracle. But, don’t despair. Oracle has something called user-defined aggregate functions, meaning that you can define your own aggregate function, or even change the behavior of a pre-defined one such as MAX, MIN, SUM.
User-defined aggregate functions are used in SQL DML statements just like Oracle’s own built-in aggregates. Once such functions are registered with the server, Oracle simply invokes the aggregation routines that you supplied instead of the native ones.
User-defined aggregates can be used with scalar data too. For example, it may be worthwhile to implement special aggregate functions for working with complex statistical data associated with financial or scientific applications.
User-defined aggregates are a feature of the Extensibility Framework. You implement them using ODCIAggregate interface routines.
For more information see chapter 11: User-Defined Aggregate Functions in Oracle9i Data Cartridge Developer’s Guide.
Filed in Oracle with 3 Comments | Tags: aggregate, function, user-defined
