Demystifying AJAX for the PL/SQL Developer

Raise your hand if you haven't heard that AJAX is the web development buzzword du jour. Despite the word "Javascript" in the acronym -- Asynchronous Javascript and XML -- PL/SQL can play a role in the front and particularly the back end. PL/SQL developers have the kind of background that's needed to understand what's happening at the lowest levels of network requests, perceived performance, and dealing with result sets. Don't be intimidated by the conventional wisdom that you should leave all the details to someone else's Javascript library. The code you need to write isn't all that big or complicated compared with 2-phase commits and bulk SQL.

Why AJAX at all? Think of it purely as an aspect of performance. You know how you run the UNIX 'time' command and see that a process took 10 minutes of user time, but only a few seconds of system time? Or how a PL/SQL anonymous block that runs for a long time produces no output from DBMS_OUTPUT.PUT_LINE until the very last moment, then dumps all the text at super speed? What you're dealing with is actual versus perceived performance, and web pages suffer from the same conundrum.

For example, when you load a page with a lot of graphics, the way the page is structured determines whether you'll see the text appear while the graphics are still loading, or if the page will be blank until every graphic is ready to display. Ditto with Javascript. If you have a big block of Javascript code on the page, the page might not render -- or might render but not respond to keyboard or mouse input -- until the Javascript is finished loading or (in worst case) until it's finished running. You can do some things to partially work around this, like hooking into the onload event handler, but still the page can seem slow, even though you know the back-end processing that generates it takes no time at all. The slowdown is in transmitting the code and data for the Javascript part of the page, and the browser parsing it and acting on it.

AJAX deals with that problem by making just-in-time requests for additional data, not when the page is loaded but when some action is taken, like clicking a button or link. The Javascript code receives the data as a block of text. Ideally, that text is supposed to be XML (hence the X in AJAX). But often, the most pragmatic approach is to send plain text, or HTML tagged text that is only a partial document. These approaches have even spawned their own acronyms; I like one known as AHAH, where the request returns a block of HTML that is inserted directly into the page at a designated spot.

If you're still waiting for me to mention something related to PL/SQL, I have good news and bad news. Bad news first: to do any of the "modern" web techniques, you will need to use a combination of at least 2 languages, in this case PL/SQL, Javascript, and optionally something else like PHP. The good news is, all of the components of an AJAX solution can be generated as PL/SQL stored procedures exposed through mod_plsql. That is, one stored procedure can generate the original HTML page that does the fancy AJAX stuff; another stored procedure can generate the Javascript loaded by the <script src="..."> tag; and yet another can do a database query or grab information through UTL_HTTP, UTL_FILE, etc. and return the text that gets passed to Javascript.

That last part is the most powerful, but also the biggest mental hurdle to get past. You are used to writing a procedure that generates an entire HTML page. Now you are writing a procedure that acts like a function, using HTP.P() to build a result set that the other end will parse as a single text string. (That's why a developer used to enterprise-class programming would probably choose to use a real XML document as the medium of exchange, to guard against the carelessness that comes with free-format text.)

On the Javascript end, you need a function to create the object that does the network request. You need a function that responds to some event on the page and initiates the request. And you also need a callback function that wakes up when the data is ready, receives the output, and then does the needful to change the page content and/or form elements. PL/SQL is really only required to do the database query and return the text of the results; the actual page and the Javascript code could come from static files, PHP, or some other option.

Within the HTML page that the user interacts with, you might also find that you need certain structures to be in place, or certain elements to have IDs. For example, one popular technique uses an empty <div> tag like so:

<div id="content_goes_here"></div>

The Javascript can locate the div element by looking up its ID, then set its innerHTML property to make an elaborate HTML structure like a table appear at that location, instead of a blank space.

You'll quickly find when you start implementing AJAX solutions, that you need a ton of these little Javascript functions, 2 for every kind of AJAXy change you're making on the page. The need to either auto-generate such functions, or to parameterize them to handle all kinds of cases, is why people recommend using the popular Javascript libraries. But there's nothing magic happening.

This post is going to be a bit light on source code, because the PL/SQL aspect of the front end is both so simple:

htp.p(q'{
/* enormous block of Javascript */
}');

and so complex and varied depending on your circumstances:

htp.p(q'{
try ... /* throws exception only in browser XYZ */
catch ...
try ... /* throws exception if HTML not structured as expected */
catch ...
}');

The techniques I use in my own code are probably lower level than you would use in yours, because I'm not using Javascript libraries like Prototype. So I'll refer you to mainstream AJAX tutorials to learn that part.

It's more straightforward on the back end. Here is PL/SQL code for a minimal AJAX procedure:

create or replace procedure ajax_procedure
(
param1 integer := null
, param2 varchar2 := null
)
as
begin
owa_util.mime_header('text/html');
htp.p('Some text from the procedure...');
end;
/

The XMLHTTPRequest object's open() method specifies the mod_plsql URL to the PL/SQL procedure, including any parameters as name/value pairs in the query string. Once the request has been fully satisfied (readyState == 4, status == 200), the object's responseText() method will return all the text that the procedure printed with htp.p(), which typically would be built up from some database logic. If the output was a real XML document, you would retrieve it through the object's responseXML method.

Alternative Firefox shortcut for Find In Page

For a long time, I've used the Firefox option "Search for text when I start typing" to avoid having to type Ctrl-F / Command-F every time to find something on a page. But with more interactive Web 2.0-style pages perhaps using keyboard controls, now I've turned that option off.

However, you don't have to do Ctrl-F / Command-F to activate Firefox's find-in-page dialog. The / key also activates it; that's an easy mnemonic for me, because it is the same key that kicks off search mode in the vi editor. It's the best of all possible worlds -- using alpha keys as shortcuts in web apps, easy access to the Find dialog, and no carpal tunnel syndrome from multi-key "Find" shortcuts.

Posting Reports from SQL*Plus

It used to be that all you needed to do with a SQL*Plus report was save it into a file, then you had everything you needed. (Or maybe just transfer the file to a web server afterwards.) These days, with blogs, wikis, etc., you might have to be a bit more creative.

For example, I was posting the results of a SQL*Plus report onto a wiki page. At the end of the SQL*Plus script, I had PROMPT commands to state the path of the report file and the URL of the wiki page. Then I'd copy and paste those items so I could open the report in an editor, open and edit the wiki page in a browser, and copy the contents of the file into the wiki edit box.

But if you're just doing the same thing every time, why not automate it? I hadn't made extensive use of the HOST command in SQL*Plus, so I held off a little longer than necessary. Here's the procedure I settled on:

Create a vim macro file that copies the contents of the edited file to the clipboard. (vim is a multiplatform vi clone with extra features like an extensive macro language, record/playback of keystrokes, and integration with features like the clipboard.) The procedure was something like:

vim -w clipboard.vi dummy_file.txt
[Then inside vim...]
"*yG
:q

That produces a macro file clipboard.vi with the keystrokes I did while editing the file. In this case, yanking the entire contents into the special buffer named *, which puts the results on the clipboard, then quitting.

Instead of just echoing the path of the report file, use the HOST command in SQL*Plus to run vim and load the report into the clipboard.

Instead of just echoing the URL of the wiki page, use the HOST command to open the default browser to that page, optionally using the URL that goes straight to the edit form.

-- Comment out the old PROMPT commands.
-- prompt Report is in: long_name_of_report.txt
-- prompt Wiki page is: http://...
-- Replace with fully automated HOST commands.
host vim -s clipboard.vi long_name_of_report.txt
host start http://...

Couple of wrinkles that I ran into along the way...

Although I use vim interchangeably between Windows and OS X, I'm finding that on OS X, the * buffer does not seem to replace the real contents of the clipboard; it gets retained somewhere between invocations of vim, but Cmd-V pastes the previous clipboard contents. Anyway, I use this technique primarily on Windows, where the * buffer really is the system clipboard.

On OS X, you would use 'host open ' to launch the browser, as opposed to 'host start ' for Windows.

I had the Cygwin command START.EXE in my Windows %PATH% where it was being run instead of the default Windows START command. For some reason it didn't like being run via HOST, even though other Cygwin commands like LS worked fine. I renamed it to CYGSTART.EXE to keep it out of the way.

While diagnosing my problem with HOST START, I ran across a blog post by Tanel Poder, who describes the technique of spooling the report in HTML format and then using HOST START to open it in a browser.

When you're using the Wikimedia markup format (i.e. the same as used on the Wikipedia site), you might find that SQL*Plus reports map naturally to the Wikimedia table markup.

When I'm faced with automating a braindead simple editing job, or even a more complicated situation involving searching, moving, and replacing variable amounts of text, my first instinct is to look for a solution involving a vim macro, rather than writing a Perl program. I have vim on all the same platforms as I do Perl, and vim lets me try out the solution interactively and "debug" the sequence of edits more effectively than Perl does.

Sometimes, this approach is looked down on as not "real" programming. I'm curious, what's the most complex or important business process you've ever run as an editor macro (vim, emacs, or other editor of your choice)?

OpenWorld at 20,000 Feet

I hadn't been to OpenWorld in a while until this year. Definitely a different tone than in years past. In the demo grounds, less "here are features we have in individual products", more "here are products built on top of those nifty features". Lots of demo booths from industry-specific acquisitions. Reminded me of IBM's SHARE conferences from the '90s, very customer focused.

Plus, Tom Kyte has a beard now. :-)

Why Keynote is Better than Powerpoint

I had the opportunity to try Apple's Keynote for a presentation where I might normally have used Powerpoint or even OpenOffice. Rather than do a lengthy review, why don't I just summarize why the results are better than I might get from Powerpoint? After all, that's the ultimate goal with presentation software -- to do something Powerpointish, but faster and/or higher quality.

Let's leave aside interface polish in the UI (the Inspector windows) and the output (slick animated transitions). I don't think those are fundamental to the results.

IMHO, the strong points for Keynote are:

The outline view with slides down the left side in filmstrip style lets you nest slides into an expanding/collapsing tree. You can see the outline showing only the major sections of a long presentation, or see all the slides, or just expand certain sections. I immediately noticed when I exported in PPT format and viewed in Powerpoint, that the equivalent view there was incredibly cluttered, with all the slides plus all the bullet points from the slides visible in a single long list. The expanding/collapsing tree let me organize the slides more easily than with Powerpoint. Nesting related slides under a "key frame" (a slide with just a giant-sized title) helped propel the actual presentation, i.e. establishing that some slides were just going to come up for a second or two, with no exposition.

The font controls lead you very gently into the habit of writing concise text, and elaborating only as needed. Let me explain what I mean. With Powerpoint, it's a race to see how many bullet points you can cram onto each slide, by resizing the text box to allow more text. The generous spacing on Keynote slides sends the subliminal message that you're only going to get a few bullets on each slide, so make the text punchier. Then, if you're desperate to fit 1-2 more lines, you can adjust the paragraph and line spacing in very small increments until the extra lines fit. The page still doesn't look cluttered, and you don't have to endlessly shrink the font into unreadability.

The export options let you choose output formats like PPT, Quicktime movie, PDF file, individual images, even Flash. Although I didn't use these, I can see that they would be tremendously useful. I exported to PPT so my audience could view the slides with Powerpoint or OpenOffice. Depending on the context of the presentation, I might have chosen Quicktime movie or Flash, to post the presentation on the web for viewing directly in the browser.

Learn PHP In 5 Minutes

Maybe you've noticed a theme in some of my blog posts, the idea of switching back and forth between languages, or quickly transitioning from one language to another.

Recently, I found myself writing some PHP code after a long stretch of doing nothing but Perl, Python, and Javascript. I decided to make it more challenging by writing the syntax from hazy, long-ago memory, and only looking anything up after the fact, to fix the syntax errors. That experience reinforced my feeling ever since Perl came out, that every recent language can be thought of as a series of small deltas to existing languages. (C'mon, don't tell me you didn't look at the little-used report format of Perl, and say to yourself "hey, this is straight out of COBOL!" :-)

So, here's my 5-minute quickstart for PHP. (No APIs, just basic language features.)

Semicolons are mandatory as in Perl, not optional as in Javascript.

Inclusion is via the 'require' keyword, or 'require_once' if everything is requiring everything else. What goes in the included files is flexible as in C and Perl.

Variables are done like Perl, introduced by $. Unlike Perl, arrays and hashes are also notated with $, no @ or % to be found. All subscripts are square brackets.

If, braces, for, while, arithmetic, etc. are like C, C++, Java, and Javascript. No "elsif", it's "else if". Comparison operators are == et. al.

Just to confuse you, HERE documents are introduced by a <<<, 3 instead of 2 like normal. There's a semicolon after the final HERE / EOF / END / etc., but none on the initial line.

Output is largely echo as in shell scripts. Variable interpolation the same as shell scripts and Perl, i.e., the meaning of double quotes and single quotes. String concatenation is . like in Perl, not + like in Javascript. (Use + with strings and you'll get a numeric 0 result.)

Ternary conditions require more quoting than in Javascript, if you're used to stringing together several object-detection tests in a single assignment statement.

Comments are a mishmash of several languages. /* */ like everything except Perl, // like Javascript, # like Perl.

Functions are introduced with the 'function' keyword, and parameters are named (including the $) but not typed. E.g. 'function myfunc($myparm) {...}'.

And that's it. Regular expressions, the other interesting aspect of most language syntax, are done with function-style notation, so on a technicality I don't consider them part of the 5-minute tour.

Separation of...

You hear a lot about the merits of "separation of X and Y", for many values of X and Y. Content and presentation. Church and State. Running people and scissors. I thought I would just touch on this subject briefly before delving into more detail on the PL/SQL APIs for web development, like the HTP and HTF packages.

In web development generally, it's conventional wisdom that you want to separate content (HTML) and presentation (CSS). It's useful to understand how that works if your HTML is generated from PL/SQL. Essentially you're abstracting out certain parts of the markup so that maintenance is easier down the road.

Instead of:

<p style="border: 1px solid black;">

you're writing:

<p class="thing_that_deserves_solid_black_border">

filling in, of course, a semantically meaningful name for the class. It's self-documenting, smaller file size (assuming you don't use ridiculously long names), and the definition of how the classes look goes into a CSS file. The CSS in turn can be a static file, generated by mod_plsql the same as the HTML page, or even produced via some other language like Perl, so there's more flexibility in making global changes.

The same notion applies in pages generated from PL/SQL, but the simplicity of HTP.P() makes it easy to overlook. The Java world is hot on the "model/view/controller" architecture. How does that play out in PL/SQL?

Basically, it just means that you have separate procedures for manipulating data, and rendering the data as a web page. Something as simple as:

create or replace procedure generate_some page as
begin
  build_giant_data_structure();
  format_giant_data_structure();
end;

The conundrum is that you know in your head that HTP.P() is just stuffing the strings in a big data structure anyway, so why not code something like:

create or replace procedure generate_some page as
begin
  generate_start_of_page();
  for item in (select * from some_table)
  loop
     generate_piece_of_page(item);
  end loop;
  generate_end_of_page();
end;

But, what if you want to write stored procedures that can be called from Java, Perl, VB, or some other environment? In that case, they'll have all their own printing logic, and it will be convenient to have stored procedures that purely work on the data and don't do any HTP.P() calls of their own. You'll want to centralize things like cursor declarations, type declarations, and constants in packages, where otherwise you might feel fine with putting those inside a particular procedure, if only that procedure uses them.

Oracle Database 11g Released, Read All About It

That would be in the Oracle Database 11g documentation, natch.

PL/SQL Web Programming 1.0 - The URL is the API

Back in the transition between 8i and 9i, I almost had the chance to write a book about developing database-driven web apps for Oracle. But with so much web stuff moving to Java and the middle tier, it was too big and amorphous of a subject. I wrote about specific features, like PL/SQL Server Pages, but not a whole soup-to-nuts treatment.

Ironically, now that things are more sorted out, I know the subject a million times better from writing code, but I'm no longer writing doc. So my (unofficial) thoughts on the subject go here on the blog.

The notion that will really get you started with PL/SQL web programming is, realize that with typical PL/SQL web apps, your API is really the URL. You write stored procedures or packages as normal, and a component in the web server translates between URL format and the PL/SQL parameter types. If you have a full-blown Application Server installation, that component is the mod_plsql module. If you take the simpler way to web-enable your database, e.g. for a departmental application, that component is called the Embedded PL/SQL Gateway and comes with the database.

What do I mean by "the URL is the API"? Well, by looking at the URL, you can see how the application code is structured. Consider this URL that I own on Oracle.com:

http://www.oracle.com/pls/db102/homepage

/pls is a common convention for the virtual path under which all the URLs represent PL/SQL stored procedures, similar to the way you see /cgi-bin for Perl or other types of scripts.

db102 means the stored procedure is owned by the DB102 schema.

homepage means that's the name of the stored procedure. It could also be a synonym pointing to the stored procedure. If the stored procedure was part of a package, the last part of the URL would be pkg_name.proc_name instead of just proc_name.

I find it a little annoying the way dots are significant in the last part of the URL, because it keeps me from using URLs that end with "filename.ext", as is typical of other web development environments. There's always the Apache module mod_rewrite to mangle the paths of URLs into some friendlier form, but that introduces complexity, and involves coordination with other groups if you don't own the web server.

But the path part of the URL is just the appetizer. The real API is the query string, the part of the URL after the "?" character. For example, in a URL like this:

http://www.oracle.com/pls/db102/search?remark=quick_search&word=john+russell&tab_id=&format=ranked

the part after the ? represents the parameters to the procedure. (The top-level subprograms that you call to generate a page in a PL/SQL web app are always procedures, not functions.) We can see that the procedure takes a REMARK parameter, a WORD, parameter, a TAB_ID parameter, and a FORMAT parameter.

Perhaps there are other parameters, but if so, they must have default values, which makes them optional for the URL. We can't infer anything about the order of the parameters; they can be specified in the URL in a different order than they're declared in the procedure, and mod_plsql takes care of it.

Each parameter is part of a name/value pair separated by the = sign. The & character is the separator between each name/value pair. Since there's nothing between the = sign and the & character following TAB_ID=, that parameter is getting passed a null.

The + in the parameter value "john+russell" above represents a space, in the standard web URL encoding scheme. Because we need a way to pass special characters like &, =, space, /, characters outside the ASCII range, etc. we can represent them as %xx, where xx is 2 hex digits representing the character code. Space is represented by either + or %20 (ASCII 32). And it follows that real % and + characters are represented by their own %xx sequences.

Your PL/SQL procedure can receive VARCHAR2 values with all of these characters with no trouble, the mod_plsql module translates all the values for you when it invokes your procedure. But if you generate a page of HTML from a PL/SQL procedure, and that page includes links to other PL/SQL procedures including parameters, then you do have to URL-encode the value part of each name/value pair.

Encoding most special characters is a relatively straightforward matter, but you have to take care with the characters that are special within the URL. For example, you wouldn't URL-encode the part of the URL that normally includes / characters, or up to the ? character that begins the parameters. (That last portion of the URL is known as the "query string".) Within the query string, you wouldn't URL-encode the = characters in the name/value pairs, and you especially wouldn't URL-encode the & characters that separate the name/value pairs. In fact, to be strictly correct, you must turn each & separator character into the sequence &, so that the browser or whatever user agent parses your HTML file doesn't find a lone unescaped & character in the middle of a string literal. That makes for invalid XML and will trip you up when you start using XHTML content-type declarations, strict file validators or link checkers, and so on.

The & business is the trickiest part of basic PL/SQL web development. Normally the & character is used by SQL*Plus for substitution variables. So even to use it in a program compiled by SQL*Plus, you either have to issue SET DEFINE OFF to turn off substitution variables entirely, or do something like

define amp = &
define urlamp = '&'

and put &. in your strings everywhere you want a normal &, and &urlamp. everywhere you want &. So your search-and-replace code that replaces & with & winds up looking like:

expanded_separators := replace(uneexpanded_separators,'&.','&urlamp.');

When a link coded like <a href="proc?parm1=val1&parm2=val2"> is displayed on a web page, any preview you see in the status bar will just show you regular & characters. There is a bit of a Catch-22 in that some older crawler software will fail to parse the & characters correctly, so your Apache log might fill up with invalid URL requests even though you coded the links according to the standards.

You must order any substitutions so that once you have introduced & or % characters into your encoded string, you don't substitute those characters a second time.

So, let's deconstruct an imaginary URL:

http://www.example.com/pls/my_schema/my_proc?parm3=val3&parm1=weird+string%28value%29%26%3D%2F%3C%3E

That means somewhere in your code, you have CREATE OR REPLACE PROCEDURE MY_PROC...

We put param3 first in the URL because the order of parameters in the URL doesn't need to match the order of parameters in the procedure; you just have to fill in values for all parameters that don't have default values.

The fact that we omitted parm2=val2 means the procedure will just use the default value for that parameter.

The value for parm1 includes some unusual characters that get quoted as + (space) or %xx (punctuation and non-ASCII characters). Your procedure doesn't have to decode the escape sequences, but it does have to encode them if you write out such a link.

The above URL with the & characters is what you would literally open up in your browser. But if you were writing out a link tag in your own page, you would write it as:

http://www.example.com/pls/my_schema/my_proc?parm3=val3&parm1=weird+string%28value%29%26%3D%2F%3C%3E

with & instead of & as the separator.

What about datatypes, you ask? mod_plsql will helpfully convert values to PL/SQL datatypes where possible. Most parameters can be declared VARCHAR2 if you want. NUMBER and INTEGER are also OK.

I stay away from anything with too exotic a representation, e.g. DATE. Better to pass it as a VARCHAR2, then call TO_DATE(param_value, 'format_string') inside the procedure. If there is a problem with the conversion inside the procedure, you can trap the exception and handle the error yourself. But if the error happens while mod_plsql is converting the value to the type of your parameter, you can't trap the exception and you will see an unhelpful (to the user) PL/SQL stack dump instead of the regular page.

Boolean is one common type that isn't supported for parameters in a mod_plsql URL. Use INTEGER and pass a 0 or 1 instead. The non-support for Boolean is handy in that you can put a required Boolean parameter on a procedure, which ensures that it can't be called unexpectedly by someone constructing a mod_plsql URL, trying to hack into your pages. Although I wouldn't rely on that as the primary security for a procedure, since mod_plsql could always add support for Boolean parameters.

You also commonly see URLs with the same parameter repeated multiple times, such as:

http://www.example.com/pls/my_schema/my_proc?name=Joe&name=Fred&name=Sue

It feels intuitive that the procedure is getting an array-like data structure with 3 elements. But what type should it be exactly? That's not quite so intuitive. If you use a TYPE declaration to make a new kind of VARRAY in your own procedure, mod_plsql can't see it early enough for you to declare parameters of that type. So what you really need is a package that declares a type that's a VARRAY or nested table of VARCHAR2s, then declare the procedure parameter as YOUR_PACKAGE.YOUR_TYPE. Luckily, there is a predefined type like this, OWA_UTIL.IDENT_ARR. But the elements of that type aren't especially long, so if you need to pass sizeable values, you'll need to make your own type.

A further complication comes if you want to make a multiple-occurrence parameter optional, that is, to make both of these URLs work:

http://www.example.com/pls/my_schema/my_proc?name=Joe&name=Fred&name=Sue
http://www.example.com/pls/my_schema/my_proc?nickname=Wheezy

You need to declare a default value in your procedure for the NAME parameter, but how do you do that for a type that you just declared? You can't just make the default be NULL. You need to make a real but empty instance of that type.

The answer is to declare inside the same package as the type, an empty instance of that type (for index-by tables a.k.a. associative arrays), or a function that returns an empty instance of that type (for nested tables or varrays). Something like:

-- In the package spec.
type type1_t is table of varchar2(100);

type type2_t is table of varchar2(100) index by pls_integer;
empty_type2 type2_t;

-- In the package body.
function empty_type1 return type1_t is
begin
return type1_t();
end;

Then you can declare your procedure like so:

create or replace procedure my_proc
(
  name my_package.type1_t := my_package.empty_type1(), -- Construct nested table with no elements
  nickname varchar2(100) := null
)
...

or

create or replace procedure my_proc
(
  name my_package.type2_t := my_package.empty_type2, -- Construct associative array with no elements
  nickname varchar2(100) := null
)
...

With parameters set up like this, you can call the procedure with one or more NAME values, or leave out the NAME parameter and pass NICKNAME instead.

A useful pattern for procedures that generate a web page is to have null or empty defaults for all parameters, and make the procedure display a minimal page (typically with HTML form elements) when invoked with no parameters, and display a more elaborate page when invoked with parameters. For example, a search page invoked with no parameters can display just the search box. When invoked with parameters, it can display results too. Instead of pointing the form tag to a different URL, point it to the same procedure. For example, if the procedure name is SEARCH, generate a form tag like so:

<form action="search" ...>

This example also illustrates how procedures within the same schema can link to each other without elaborate paths or qualifiers in the URLs. Just put the procedure name in the action= attribute of a form tag, or the procedure name + the query string in the href= attribute of an anchor ("a") tag.

Planning and preparation like this is all well and good, but how do you actually generate the HTML for the page? Ah, that's another post coming up!

Search Engine Optimization for Programming Languages

Why didn't language X take over the world? Why does language Y enjoy broad adoption despite clear shortcomings? While there are lots of factors, for example the degree of strictness vs. whimsy and how that matches up with the mindset of programmers, I am convinced that it largely comes down to how easy it is to search for sample code in a given language.

For example, once Java became popular, if you ran across a mention of some class or method name, you could always find lots of example by searching for those names. Or if you wanted to see how to use a certain GUI method in combination with a certain container type, you could search for pages with both names. If any name was ambiguous, you could add some other keywords -- class, extends, etc. -- to make sure you really found a full code sample and not some general discussion.

So, Java's adoption was helped by a confluence of factors: a standard set of relatively verbose names, and a bunch of students posting their homework solutions online.

Perl, for me, didn't fulfill its promise and I lament sometimes that it didn't take over the world like it could have. When your source code is full of idioms like $_ and <>, it's hard to search for code samples. (By search, I mean using Google et al, rather than using vi, emacs, or grep on your own source.) When I look at my Perl code, the words inside are mostly generic like for, if, print, and die. Searching for them, you would come up with lots of non-programming discussion, or source code from other languages. For example, what does "unset local $/" do? Google will find instances of the phrase "unset local", but not the full line of code.

So, Perl's adoption was hindered by factors related to searchability. The best Perl source on the web is posted as CGIs, where a search engine crawler can only spider the output, not the underlying source. It's hard to find sample code generally. And the way Perl modules are documented, when you search for popular module or method names, the top hits tend to be dozens of copies of the same Perldoc files. (And if the Perldoc was sufficient, you wouldn't be doing the search in the first place.)

I see SQL and PL/SQL as somewhere in between Java and Perl in terms of searchability. There are lots of unique names from PL/SQL packages and methods. Some of the words that might otherwise be too common are grouped into phrases like "connect by" and "order by". PL/SQL has some words like begin and end that aren't especially searchable by themselves, but if you combine them with others like declare, exception, etc. you can devise searches that home in on PL/SQL source code.

Where SQL and PL/SQL get into trouble with searchability is in assigning specialized behavior to single, common words. PL/SQL has a function called TABLE(); good luck in finding that by searching given that TABLE is used in so many other contexts like CREATE TABLE or "Doing xyz to a Table". There also used to be a SQL function called THE(), now happily gone, but I still get requests from time to time "how come I can't search for the THE function?".

When I get some free time, I'm going to look into the idea of auto-classifying source code via Bayesian analysis. Oracle Text has the CTX_CLS package, where you feed it training data consisting of already-categorized documents, and it deduces rules so that it can analyze unknown documents and match them up with those same categories. The Oracle doc library has tons of files with source code in a single known language, multiple languages in the same file, or source code from one language in a book that is primarily for a different language. I'd like to compare and contrast the CTX_CLS approach with a "homegrown" one similar to Peter Norvig's spelling corrector.