Monday, May 29, 2006

Invoking User-Defined Database Functionality from within and outside the database

In my previous post i touched on the fact when database access is significant, it is often more efficient to implement and run user-defined functionality directly in the database.
In his dated but still good report on distributed computing economics (i.e., Grid Computing) Jim Gray's recommends to "put computing as close to the data as possible in order to avoid expensive network traffic". See
This is true for most RDBMS; as far as the Oracle database is concerned, this is true for PL/SQL, Java, and Web services (i.e., Database Web Services).

Now, assume you have built user-defined database functionality, how do you invoke it from within and from outside the database? With the Oracle database, there are three cases:

1/Invoking PL/SQL based user-defined database funtionality

Assume Func_nn(),Proc_nn(),Pkg_xx.Func_nn(), and Pkg_xx.Proc_nn()

  • Invocation from SQL, PL/SQL, and SQL*Plus
    SQL>CALL Func_nn();
    SQL>CALL Pkg_xx.Func_nn();
    SQL>CALL Pkg_xx_Proc_nn();
    SQL>Begin Proc_nn() End/
  • Invocation from Java (J2EE, JDBC, Java-in-the-database): same as Java-based functionality see below.

2/ Invoking Java-based user-defined database functionality

  • Invocation from Java-in-the-database: A user-defined Java functionality is transparently invoked by another Java class running in the database, provided the invoker has the proper privileges and authorization.
  • Call Specifications: Java-based functionality is made available/known to SQL (therefore to PL/SQL, and JDBC) through a PL/SQL Wrapper called Call Spec. There exist top-level Call Spec,PL/SQL-packaged Call Spec, and object type Call Spec. Call Spec must map parameters and retur values to/from SQL or PL/SQL types to Java (and JDBC supported) types; such mapping can be challenging unless you use a tool (i.e., JPublisher).
  • Invocation from SQL, PL/SQL and SQL*Plus: similar to PL/SQL-based functionality (see above).
  • Invocation from Java/JDBC/J2EE using the SQL 92 Syntax CallableStatement cstmt = conn.prepareCall(“{? = call func(?, ?)}”);
  • Invocation from Java/JDBC/J2EE using the PL/SQL Anonymous Block syntax CallableStatement cstmt = conn.prepareCall(“begin ? := func(?, ?); end;”); Note: with JDBC programming, Output parameter(s) must register the data types and the value(s) of Input parameter(s) must be set. However,not all SQL database types are supported.
  • Invocation from Java/JDBC/J2EE using Client-Stub: a JPublisher-generated client-side stub allows callingmethods on Java classes in the database, without the provision of a user-definedCall Spec (see above), and without their limitations (of Call Specs).
  • Invocation from OJVMJAVA: the Oracle database furnishes an interactive command-lineutility called OJVMJAVA, which lets you run Java classes in the database (i.e., classes with a public static main() method) from the client machine.

3/ Invoking User-defined Database Functionality as Web Services Simply put, Web services consist of a service client and service provider exchanging SOAP-based XML messages using Web protocols. User-defined database functionality can be published as Web Services and invoked by by standard-compliant Web services clients. However, beyond SOAP, WSDL, and UDDI, there are more to Web services. The Oracle implementation of Database Web services benefit from the Quality of Services furnished by the Oracle AS SOA stack. The steps for turning database functionality as Web Services is described in Database Web Services portal (linked from this blog).

All these approaches are fully decribed in my book

In my next post I'll touch on invoking external functionality from within the database.

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.