Wednesday, May 03, 2006

User-Defined Database Functionality

Beyond the out-of-the-box built-in RDBMS functionality, you often need to extend the capabilities of your database by developing your own database-resident functionality, or data logic.

The requirements for user-defined database functionality range from performance (see free-sample chapter in previous posts), integration with existing functionality, security, ability to share data logic (across all clients applications), cost reduction, skill reuse, deployment/topology choices (see dilemma below), and many other reasons.

The following applications are candidates for being implemented as user-defined database functionality:
- Traditional data logic: SQL statements grouping
- Content/Repository management
- In-database Message Queuing and propagation
- Reporting functionality: generate pdf, excel, and XML documents from relational data
- Data-based Alert, and Notification
- Expressions Parsers (see code fragment below)
- Key generation, MD5 CRC checksum
- Multi-media data processing: store, retrieve, transform/convert images (GIF, PNG, JPEG), audio, video, or heterogeneous media data. Alternatively you can just use Oracle interMedia, a packaged multi-media processing framework.
- Text data management: index, search, and analyze texts stored in RDBMS. Alternatively you can just use Oracle Text, a packaged text data processing framework.

However, an RDBMS is not an application server, you must make the determination that the user-defined functionality pertains indeed to the RDBMS.
- tax and rate engines, which take input values and return tax amounts or rates, are better implemented as EJBs and do not belong to the RDBMS.
- a JavaServer Pages that generate user-interface rendered in a browser does not belong to the RDBMS.
As discussed in many forums, making the determination is not always a clear cut.
- a frequent dilemma is the choice between caching data in the middle-tier (i.e., CMP Beans, POJO) or pushing the processing closer to the data (i.e., stored procedures)? See
- a server-side UI can be produced using either a JavaServer Pages (that is deployed in the middle-tier) or the database-resident Oracle Application Express (formerly known as HTML DB), or use the HTP package, directly in the database.

The mechanisms and programming models for building user-defined database functionality
range from proprietary procedural languages (e.g., PL/SQL, T-SQL), standard languages (e.g., Java), de facto standard languages (C#, VB), messaging systems for database messaging, job/task scheduler, and so on.

The Oracle database furnishes PL/SQL, Java, Streams AQ, DBMS_SCHEDULER and so on for building user-defined functionality.
There are tons of PL/SQL examples, see
Java in the database is an open alternative to PL/SQL for implementing advanced functionality such as a domain-specific language parser. Imagine an Excel expression evaluator/parser that can be used to generate formulas representing complex interrow calculations in the database and dynamically generating the corresponding SQL.

Mini Parser code fragment
public class Mini parser {
package example;
import example.parser.parser;
import example.formula.Expr;

public static String eval(String spreadsheetExpr) throws Exception {
parser p =
new parser(new StringReader(spreadsheetExpr));
Expr expr = (Expr)p.parse().value;
return expr.eval();

public static void main(String[] args) throws Exception {

Then the following query returns 15 which is not the generated SQL, but gives you an idea of how the expression evaluator works:

select eval('=sum(1,2,3,4,5)') from dual;

The complete mini-parser source code as well as the corresponding CUP and LEX descriptions are available in chapter four of my book (see link on this blog).

In the next posting, I'll elaborate on how to make the user-defined database functionality available for invocation by other database components as well as external components.


Catherine Granger said...

Hi Kuassi,

so where do you put object-relational mapping? Is that also a User-Defined Database Functionality?

Kuassi Mensah said...


Good point however, unless an OR mapping framework is hosted direclty within the RDBMS -- didn't this recall you the infamous OODBMS? -- OR mapping will happen in the middle or client tiers and hence cannot be viewed as database-side user-defined functionality.

Having said that user-defined database functionality can participate to OR mapping. See my reponse to similar question on the following blog:

Anonymous said...

I copy/paste your code example and it doesn't even compile!!!

Anonymous said...

What do you think of Oracle/other DB vendors exporting some of the core db processing logic as APIs in C/Java...

Ex. sorting, joins, searching, n-memory tables, ...

As of now your code and data need to live in/or swtich context by making stored procedure call to the DB tier, to make use of these features .. so this does not exaclty fit in as 360 degree programming ...

Kuassi Mensah said...

>I copy/paste your code >example and it doesn't even >compile!!!

Of course, it won't compile, this is just a code fragment to get to the point. As i have mentionned, the complete mini-parser source code as well as the corresponding CUP and LEX descriptions are available in chapter four of my book (see link on this blog).

Kuassi Mensah said...

> What do you think of
> Oracle/other DB vendors
> exporting some of the core
> db processing logic as APIs in C/Java...

Interesting comment, only speaking for Oracle, i doubt that any of the core db processing be exported outside of the DB. In fact the core Oracle DB functionality is generated as C/C++; then some surrounding core functionality such as the Java VM runtime are generated Java classes that are installed by default but run in interpreted mode (you need to install the native compiler libraries to run the system classes as well as user-defined Java classes in natively compiled mode).
Having said that, as i like to say, the RDBMS is not an application server so the removal of the J2EE stack from within the database is a sound choice, and there might be other functionality that are better fit for a middle-tier but then these are out of the scope of this blog (maybe i should start another blog on user-defined middle-tier functionality :)).

Regarding the last segment of your comment all the user-defined database functionality i am talking about (including the mini-pasrer) run/execute within the dtabase.
Vut as i will touch on in following posts, 360 database programming also involves calling user-defined database functionality from within the database, from outside the database and calling client-side or middle-tier functinality from within the database (this last one is a fast growing trend that many of you are adopting, big and exciting topic, stay tuned)
Hope this clarifies.

Herod T said...

Never do any processing in the middle tier. Why have the middle tier process the tax amount when the database can do it faster with better scalability and better security and store it for later use and/or auditing?

The middle tier will be replaced with some brand new widget gizmo some high paid marketing company has told everybody is great and everything will have to be done .

Do it in the database. It has been here for decades and will continue to be here for decades more.

The only candidate of yours that I would settle with being in the middle tier is the report output generation - converting output to PDF etc. But, I would argue the point that it shouldn't be done in the middle tier, instead it should be a program that the database calls to generate the information then the output stored in the database then the output presented to the user. The output is cleaned by the database later.

Nothing else.

Never take the data away from the database and process it and put it back. The only exception to that rule is when a human being has to process the data.

Kuassi Mensah said...

Great comment, i agree with most things you said however don't you think this is a too radical approach?

Herod T said...

Not radical at all.

Data is "forever"
front ends change.

Anonymous said...

I hope this meets you in fine time .I am munch intrested in oracle i am about to finish OCA EXAM by this month.can you people refere me to some one in oracle india.
Thank you
A.mohamed suhail

Esteban Capoccetti said...

Hi kuassi, glad to read from you again,

Herod T; I don't know if you have already read the following article:

APPLICATION DEVELOPMENT" and shows the different architectures we can adopt when developing an application,where Bussines/Data Logic should reside. I recommend this article.

After having using Oracle Database functionalities for more than 7 years I can confirm that the "thin->thin->Fat" approach is the most convenient for any new project, and even more convenient if you have to meet a deadline!!; but this is MY opinion.

Kuassi, thanks for your interesting blogs.

Esteban Capoccetti.

Anonymous said...


Anonymous said...


Edwin said...

Buy Kamagra
Earn Google
Viagra Cialis
Cheap Kamagra
Cheap Viagra
Cheap Cialis
Make Money on Google
M65 Jacket
M65 Field Jacket
Airline Dog Carrier
Airline Dog Carriers
Viagra Cialis
Earn Google
Airline Dog Carrier
Airline Dog Carriers
Airline Approved Dog Carriers
ED Hardy Wholesale
Copy DVD Software
How to Send Fax
14k Yellow Gold
Redneck Costume
14k Gold Heart
Tandem Baby Stroller

VUONG said...

医師 求人
医師 求人
医師 求人
医師 求人
医師 求人
医師 求人
医師 求人
医師 求人
医師 求人
医師 求人
英語 メール

会社設立 手続き
東横 賃貸
田園都市 賃貸
デザイナーズ 賃貸
恵比寿 賃貸
学芸大学 賃貸
目黒 賃貸
中目黒 賃貸
新築 賃貸
ペット可 賃貸
三軒茶屋 賃貸
債務整理 新宿
過払い 東京
液晶 テレビ 32

VUONG said...

先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
婚活 名古屋
婚活 名古屋
婚活 名古屋
婚活 名古屋
婚活 名古屋
婚活 名古屋
婚活 名古屋
婚活 名古屋
婚活 名古屋
婚活 名古屋
浮気調査 松戸
浮気調査 船橋
備考調査 追跡調査 東京
不倫調査 離婚調査 東京
浮気調査 素行調査 東京 
ストーカー相談 ストーカー対策 東京
結婚調査 結婚詐欺 東京
信用調査 企業調査 東京
調査料金 東京
身上調査 身元調査 身辺調査 東京
所在調査 東京
夫 妻 浮気 東京

vancouver website design said...

That's a good feature for reduce the complexity and easily maintain the database. Thanks for post.

r4 said...

This is just a code fragment to get to the point. As i have mentionned, the complete mini-parser source code as well as the corresponding CUP.