Wednesday, January 19, 2011

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.

"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:

Tuesday, January 18, 2011

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!

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):

Modify the package body to suit your environment (particularly the g_schema_name constant, and the is_whitelisted function)

Install the package into your schema

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.

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.

About Me

I have been working as a consultant and software developer for 18 years (since 1997), with a special passion for relational databases. I have done a fair share of work using Microsoft tools (including SQL Server, VB, ASP, .NET and C#) and other tools (most notably Delphi), but my favorite tool is the Oracle database with PL/SQL and Apex.