PL/SQL Utility Library update

I've added several packages to the PL/SQL Utility Library archive, including string, date, SQL and XML libraries. There are also some new examples in the "demos" folder.

All right, now it's Friday and time for a beer :-)

Get the latest version of the PL/SQL Utility Library from this page.

Back to the Future

Come with me on a short trip through space and time, taking a look at the history of building (web sites).


The Stone Age, circa 1995

Although it involved a lot of manual labor, mighty Stonehenge and the pyramids were built with very simple tools (or by aliens, but let's deal with that topic another day).

Web development was pretty basic back then, but conceptually simple enough.

The Dark Ages, circa 1998

Some pretty amazing castles were built during this age, and they still stand today.

The basic tools had improved a lot, but they were still recognizable. Even an Neanderthal from the previous age could probably be effective with these tools.

The Age of Enlightenment, circa 2002

The gothic cathedrals from around this time look awesome, but were (and still are) inhabited by unpleasant religious fanatics.

At this point, great thinkers with lots of free time combined art and science to build magnificent works of beauty and elegance.

The Age of the Astronauts and the Race for Space (via the Clouds), circa 2008

A lot of very expensive spaceships were built during this age.

The great empires fought a cold, mostly ideological, war to see who could be the first to reach the moon.

Of course this all ended with a ka-boom and the eventual retirement of the space shuttle program.

A New Hope, 2011

Realizing that space tourism and new houses on Mars might be out of reach for most people in the foreseeable future, there is a renewed interest in more lightweight approaches to building and transportation. You might even call it eco-friendly.

Even the otherwise lethargic Microsoft jumps on the bandwagon, to the acclaim of professional developers.

"WebMatrix is full 180 from the highly abstracted cathedral that is ASP.NET. (...) WebMatrix is focused on simplicity and the "Get It Done" developer. Which, to me, is a massive undersell as we're all "Get it Done" developers."

"... you have to use raw SQL to query the database. This is going to turn off the Ivory Tower crowd who prefer to wade through XML Soup and tedious designers - but that's to their detriment. As I've always said - the best DSL I've ever seen for working with data is SQL."

That's cool. This is what web development looks like with WebMatrix:

Looks strangely familiar, doesn't it? A mix of HTML markup and code, and direct data access without any of that ORM stuff? Yes, that's right. For comparison, let's bring up that screenshot from the Dark Ages (1998) again:

Conclusions & Take-Aways

(Because there always needs to be a take-away.)

I am (definitely) not saying that "Frameworks are evil", or that "Progress is uncool". I'm just as lazy as any programmer, but I prefer the simplest thing that could possibly work.

I prefer frameworks that don't try to obscure that what we (most of us) are trying to do (most of the time) is to generate some HTML based on data from an SQL database.

Do we really need something like this

If something like this can do the job?

The golden path is probably somewhere in-between those two extremes. But keep in mind: Stonehenge has lasted for 5,000 years, while the space shuttle is being retired after less than 50 years.

PL/SQL Utility Library

Most programmers have a collection of utilities and code libraries that they re-use for several projects.

I've created the PL/SQL Utility Library page on Google Code to host some generic utilities that I've written myself, some that I've collected from elsewhere (credits and links can be found in the relevant source code), as well as links to useful PL/SQL libraries that are actively maintained elsewhere.

Check out the links and download the source code, there's a lot of different stuff here; from parsing CSV files, integrating with Google Maps and generating JSON, to zipping/unzipping files with PL/SQL.

I plan to add several more packages as soon as I get the code cleaned up.

Drop a comment below if you know of any other PL/SQL libraries or utilities that should be added to the list!

SOAP Server in PL/SQL

Or how to expose PL/SQL packages as SOAP web services using pure PL/SQL

I have blogged before about the various options available for both consuming and exposing SOAP web services using PL/SQL. (And if you don't know what SOAP is, here is a tongue-in-cheek introduction.)

Here is yet another lightweight alternative, a small PL/SQL package that implements a simple SOAP server. It will generate a WSDL document on-the-fly for the packages you want to expose (subject to a whitelist). Functions (only) are invoked using dynamic SQL, and the results are returned in a SOAP envelope. Exceptions are handled using the SOAP Fault mechanism.

I have successfully tested this package on both the Embedded PL/SQL Gateway (DBMS_EPG) on Oracle XE 10g, as well as on Apache/OHS with mod_plsql (tested on a 10g database).

Here is a screenshot showing Web Service Studio used to test the Employee demo service (ie database package):

To try it out, follow these steps:

1. Download the source code
2. Modify the package body to suit your environment (particularly the g_schema_name constant, and the is_whitelisted function)
3. Install the package into your schema
4. If you want to run the package through the Apex DAD, remember to grant execute on soap_server_pkg to anonymous (on EPG) or apex_public_user (on mod_plsql), and modify the request validation function (wwv_flow_epg_include_mod_local) as appropriate. Create a synonym if you don't want to include the schema name in the URL.
5. Use a SOAP client such as Web Service Studio or SoapUI and navigate to http://your-server/dad-name/soap_server_pkg.wsdl?s=your_package_name

Issues and limitations

• This initial version only supports functions that return a single value (varchar2, number, date, clob). Functions returning complex (user-defined) types, object types or array types are not supported. But as the demo package shows, you can return complex values using a single CLOB formatted as XML.
• The OWA toolkit has a 32K limit on the size of CGI environment variables, which means the SOAP request body is similarly restricted. So although you can return responses of any length from your web services, the requests you can receive must be under 32K in length (including the XML tags in the SOAP request envelope).
• The Apex Listener (at least as of the EA release 1.1) differs from the EPG and mod_plsql in that it does not pass the SOAP_BODY request variable to the OWA toolkit, so this solution will not work with the Apex Listener. However, it should be trivial to add to the Listener, so if you would find it useful, then you should file an enhancement request with Oracle and ask for it.

I'll end with a note of caution: This package executes dynamic SQL. While care has been taken to sanitize the input and to implement a whitelist, you should carefully review these security measures in terms of your own environment before you expose your database on the network.