Cool SQL function: DUMP
I have noticed that, in his latest book, Tom Kyte used the SQL function DUMP quite frequently to expose the internal representation and the size of a specific expression. To be honest, I do not use DUMP very often, but I do find it useful in certain situations. Continue reading…
Cool SQL function: EXTRACT
I have always used the SQL function TO_CHAR to extract parts of a date, like the month or the year. But, there is another way to extract parts of a date: the function EXTRACT. Here is an example: Continue reading…
SYS_CONTEXT in Oracle
Oracle has a very useful built-in function called SYS_CONTEXT. The syntax of this function goes like this:
SYS_CONTEXT ( 'namespace' , 'parameter' [, length] )
SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.
What makes this function more interesting is the fact that Oracle provides a built-in namespace called USERENV with predefined parameters, which describes the current session. Go ahead and run the following query: Continue reading…
Using CUBE to generate rows
When I find a cool trick like the one I am going to tell you about here, I immediatly post it on my blog so that I can find it later when I need it, and also share it with the rest of you. I just love such tricks. A few days ago, I talked about generating rows from DUAL; There is another way to generate rows, using CUBE.
So, how can CUBE generate rows? Continue reading…
User-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.
3 Comments | Filed in Oracle | Tags: aggregate, function, user-definedNVL2 in Oracle
Did you know that in addition to the function NVL, Oracle also has the function NVL2? which is quite different than its cousin NVL. So, what does NVL2 do? Continue reading…
