Thick but Agile With Oracle
A New Way of Walking
An increasingly popular quick prototyping AGILE technique is the so called “Thick Database” design.
Thick Data Base Approach
In this technique the web developers, instead of writing his or her own Selects, Inserts, Updates, and complex data manipulation, accesses and manipulates data through a pl/sql API which is written specifically to support the application and is stored in the database. Result sets are typically passed to the application via Ref Cursors.
This approach has the potential for better performance and better control and documentation of the system architecture wrt data storage, access, and manipulation.
Techniques for reaching Oracle based services from external systems
are consistent with the AGILE Thick Database approach.
One such mechanism is the XML-RPC standard.
The XML-RPC home page is here
Providing XML-RPC service (or something like it) through Oracle
The Oracle Mod_plsql Gateway , once it is properly installed and configured, provides a mechanism for pl/sql procedures, both stand alone or packaged, to be invoked from a URL.
Typically, a web application displays an HTML form to a user.
The form’s “action=” attribute is set to something like
<FORM action=”http://oracle01.nypl.org:7777/hades/menu” >
If the method=”GET”, the arguments are appended to the url in a form such as … url?arg1=value1&arg2=value2 etc
If the method=”POST”, the arguments are transmitted on subsequent lines as if they were a document.
The rest of the HTML form is designed to solicit the arguments required by the function definition.
When the form is “submit”-ed, the get or post is directed to the named URL, which results in the specified procedure with the arguments supplied being executed.
The procedure may query or manipulate database tables and typically returns an HTML response which is delivered back to the user’s location and rendered into a visible display by the user’s browser.
It should be fairly straightforward for us to expose any number of pl/sql procedures as XML-RPC services by creating one or more procedures which take a single argument, a string or character large object containing the XML-PRC calling data. We might require that the URL part of the XML-PRC call specifies the XMLRPC “handler”.
The job of the handler would be to parse the argument document and construct the call to the particular “method” with the appropriate arguments.
Another variant would be to creaate a handler per service, in which case the method request portion of the XML-RPC argument document would be redundant (the desired method would be specified on the URI).
Either way, we dont anticipate any difficulties actually invoking the routines or passing arguments to them. There is one small annoyance however. The mod_plsql gateway is set up so that access is gained by means of a DAD (a Database Access Descriptor) which tells which Oracle Account to log on to, but the user is expected to supply a password interactively. We’ll need to specify the password to the DAD so that the full log on is handled behind the users back, so that the user needn’t supply a password.
Using XML-RPC services from pl/sql
We can use the UTL_HTTP services to call out from Pl/Sql. UTL_HTTP
We may also be able to use the HTTPUriType built in object with its getXml() or getClob() methods to do the same thing.