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 ftp://ftp.research.microsoft.com/pub/tr/TR-2003-24.pdf.
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 http://www.amazon.com/gp/product/1555583296/

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