What Is Scripting, Anyway?

[Some thoughts prompted by the article "Programming is Hard, Let's Go Scripting" by Larry Wall, father of Perl.]

How to define scripting and scripting languages? For me, as a long-time delver into compiler details, there's one key distinction between scripting languages and other ones. Scripting languages bypass all the low-level conventions for interlanguage calls, and take the shortcut of encoding everything as text. Usually, such conventions take up one or more chapters in a compiler manual -- how is a float represented, are integers big-endian, what's the memory layout of an array, structure, etc. The scripting language expects that all of this will be passed around in ASCII format via pipes, text files, and command-line flags.

Unix shell scripting is the natural building block for all of this. Consider a pipeline like this:

du -k | sort -n | grep -i Downloads

The first command gives the size of each subdirectory starting with the current directory. The second command sorts this output while treating the first field as a number; for example, "99 Directory 1" comes before "100 Directory 2", even though a strict alpha sort would put "1" before "9". And the third command plucks out lines containing a certain string; the directory names didn't get lost while we were doing that numeric sorting.

Perl descends from this lineage with its backticks, commands like eval and grep, and HERE documents. All very familiar. But Perl's object orientation really strays from its roots as a scripting language. All of a sudden, you have to think intently about how hashes are laid out. You can write script-like programs using Perl's object-oriented notation with packages written by someone else, but the OO code behind your own packages doesn't feel very scriptish. (In the same way that C isn't a scripting language, even though grep et al are written in C.)

The nicest Perl touch I see in this area is in conversion between strings and numbers. For example, you can say '$foo = "00000";', then do various operations like $foo++, and you'll find the value gets printed as "00001", "00002", etc. Very nice for formatting sequence numbers for IDs.

Early vs. late binding. Now we're getting into familiar territory for Oracle developers. By default, most PL/SQL uses early binding -- it's easy to know ahead of time which tables, columns, types, etc. a PL/SQL subprogram will use, and whether it can use them successfully. (Just look at whether the stored procedure or function is valid.) Venture into dynamic SQL, such as the EXECUTE IMMEDIATE command, and now it's not so easy to guarantee correctness or reliability. One mistake in string concatenation logic, and you'll try to reference a non-existent table or column, send badly-formed literals, and so on -- whole new classes of errors you never had to deal with before. Normally, I come down on the side that favors greater flexibility, but I haven't found PL/SQL's restrictions to be too onerous considering how important it is to ensure data integrity. Other aspects of PL/SQL that incorporate late binding -- hence are more comfortable for long-time scripters -- are the anonymous block (and it's always fun to generate the code for an anonymous block from some other program written in Perl or what have you), and the %TYPE and %ROWTYPE qualifiers, which insulate programs to some degree against changes wrought by ALTER TABLE.

Wordiness vs. use of symbols. Here is where I could imagine some improvements in PL/SQL. Why not allow { } instead of BEGIN/END, LOOP/END LOOP, and THEN/END IF for blocks? As Larry notes, scripting languages occupy the whole spectrum when it comes to verbosity.

PL/SQL also has the historical capitalization convention of all-caps for built-ins, and lowercase for user-defined names. Here, I firmly agree with Larry:

Actually, there are languages that do it even worse than COBOL. I remember one Pascal variant that required your keywords to be capitalized so that they would stand out. No, no, no, no, no! You don't want your functors to stand out. It's shouting the wrong words: IF! foo THEN! bar ELSE! baz END! END! END! END!

It's really the subprogram calls and variable references you want to see clearly when skimming code. As someone involved with doc, I know how nice it is to write "the ALTER TABLE statement" and know the emphasis will be preserved in all printed incarnations, even down to man pages, in a way that isn't guaranteed for bold, italics, or monospace. Yet a concern for code clarity means I'm perfectly happy writing INSERT, BEGIN, etc. in uppercase in running text, and lowercase in my own code. PL/SQL also has the conundrum of so many Oracle-supplied packages. Since DBMS_OUTPUT.PUT_LINE comes from Oracle, should it be written all-caps? Or since it obeys the same lexical rules as some package and subprogram you write, should it be lowercase? I prefer to stick with lowercase for everything in source code, even if that means terms can't be cut and pasted between code and running text.

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.