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.

Upgrading SQL*Plus Client

Sometimes the old ways are best, sometimes not. I have some rarely-used machines where I had never bothered to upgrade my client installation, and so were still running the 9i SQL*Plus. The older SQL*Plus could still compile all my PL/SQL source that used the latest and greatest 10g features, so what's the big deal?

As it turns out, there are a number of cases where having a downlevel SQL*Plus produces cryptic errors. I went looking in the docs, but I don't think there is any comprehensive listing of these cases. (It would be difficult to make an exhaustive list; it might not help much with troubleshooting because the errors are so cryptic; and I'm sure the workaround -- just upgrade! -- is stated many times in many places. Still, I would have liked to have found a list that showed all the errors that could occur, so I could find it by searching.)

For me, the feature that prompted me to finally upgrade was the alternative quoting syntax. It worked fine in a PL/SQL block:

begin
  insert into some_table (title) values (q'{Administrator's Guide}');

But not if I used the quoting feature directly in SQL, i.e. in a query from the SQL*Plus command line or in a SQL script with INSERT etc. not wrapped inside a PL/SQL block:

SQL> select title from some_table where title like q'{Administrator's}';
ERROR:
ORA-01756: quoted string not properly terminated

What's the Capital of Abyssinia?

Few subjects provoke as much passion as the question of how to capitalize programming code.

You've got C-like languages where case is significant, but the discussion continues whether to string_words_along or JamWordsTogether. My own convention for PL/SQL is a bit idiosyncratic. I decide whether a particular subprogram is more procedural or more object-oriented, and use underscores or Camel case as appropriate for each one.

Then you've got lots of other languages, like PL/SQL, where case is not significant. But you'd still like to format source code for readability and comprehensibility. I commonly see the convention of reserved words in all caps, and lowercase for names of tables, variables, etc. That allows for some degree of consistency in documentation -- you can talk about "the IF statement" and the reserved word is easy to pick out even if the text is stripped of all formatting in a man page or an e-mail. But if Oracle ships packages like DBMS_OUTPUT, does it make sense to capitalize those names as if they're reserved, or treat them more like user-defined objects? Rather than drive myself to distraction, I use lowercase for everything in source code (after all, nobody ever accused C or Perl code of being hard to read... er... :-). When referring to reserved words in text, I use all-caps.

Now, I am well aware that there is an uppercase stigma attached to some languages. FORTRAN 77 was case-sensitive and got endless grief for that. (Why, I left my small-town roots rather than accept a job working in FORTRAN.) Fortran 90 enabled the use of lowercase, which was such a big deal that it was reflected in the language name. At IBM, where I eventually worked my way up to working on XL Fortran :-), it was common to switch within the same sentence: "The IBM Toronto Lab took over the FORTRAN mission, and released several Fortran compilers, while the mainframe FORTRAN products continued to be developed by the Santa Teresa lab". This after years of working on Ada, which also had an uppercase stigma because everyone assumes it should be ADA. (It's Ada, because it's an eponym not an acronym.)

There is some uppercase lurking inside the database. Issue create table foo and it will dutifully create a table named FOO, as you can see by 'select table_name from user_tables'. You can make the table be named FooBar by enclosing the name in double quotes: create table "FooBar". But then you have to put the quotes everywhere you refer to the table, so it's not worth the trouble except in unusual circumstances where you want to obscure the name, or have names identical except for case.

The tilt towards uppercase means you more often see code like this for doing case-insensitive matches:

select last_name from my_table where upper(last_name) like 'A%';

You could just as well write:

...lower(last_name) like 'a%';

and people might regard that line with more respect. These days, out of principle, I always use a regular expression instead:

select last_name from my_table where regexp_like(last_name,'^a','i');

In real code, anyway. On the command line, I still slip back into LIKE out of habit. It puzzles me a bit that SQL can't handle the BOOL... bool... Boolean datatype of PL/SQL, yet regexp_like is testing a Boolean condition without any comparison operator.

Why Is It So Hard to Print?

A knock that seems to apply to any "enterprise"-class language is, why is it so hard to actually print anything? For purposes of this post, we'll consider PL/SQL and Java.

In Java, a hello world program must call System.out.println(). That 3-level call is kind of verbose, plus in the CamelCased world of Java you might forget whether the second part is "Out" or the third part is "PrintLn".

In PL/SQL, a hello world program must call dbms_output.put_line. Don't forget to issue "set serveroutput on size 1000000" in SQL*Plus first, or the output is held in a buffer instead of printed immediately. When you see this call in the docs, it will probably look like DBMS_OUTPUT.PUT_LINE, which will make you think it's combining the keywords of Pascal and the capitalization of FORTRAN 77. In the Oracle9i PL/SQL Guide, I had deep metaphysical anguish over whether to show such a hello world example in Chapter 1, or if it had to wait until much later after discussions of stored procedures and packages. Of course, I chose to break with linearity and show it early.

In contrast, scripting languages like Perl and Python let you just issue: print "hello world". Why make it hard to do simple I/O? How is that a selling point for a language?

You can think of it as an implicit nudge towards a particular style of programming. Both Java and PL/SQL would prefer that you write your own function named print(), or msg(), or debug(), or whatever that is essentially a 1-liner that calls System.out.println or dbms_output.put_line. That's because the program that today just prints something to the screen, tomorrow might store the output in a database, or send it in an e-mail, or transmit it in some other way, or maybe send it to multiple output sources. The idea being that it's easier to change your one print() function than to go back and change every system-defined print call to your own function name.

With Java, you might have several kinds of objects, and in each one print() might do something different.

With PL/SQL, you might define print() as a nested procedure inside a bunch of other procedures, or each package might have its own print() function. PL/SQL makes the "store in a database" option so easy, that you might find it's the most convenient way to go for any function like debug(), log(), etc.

Unix hackers might find all of this unnatural, because in the environment where you would normally be doing Perl or Python scripts, you could easily redefine where the output goes by way of pipes, redirects like > or 2>&1, or the tee command.

However, the zen of writing a bunch of seemingly trivial one-line functions is part and parcel of both object-oriented and functional programming, so just cast your mind back into LISP hacker mode if necessary and it won't seem so painful.

Less is More, More or Less

Do you fret every time you declare a PL/SQL variable, that you've carelessly allocated a few unnecessary bytes, and that this wastage will be replicated thousands of times as your code is called from a bazillion sessions, and that that will be the straw that breaks the camel's back and makes your server thrash to a halt?

Well, calm down. It's not that bad.

If you don't know exactly how much space is needed for a PL/SQL variable, the most space-efficient thing you can do is counterintuitive: declare a gigantic variable. For example:

declare
  email_address varchar2(32); -- Could give error from being too small
  email_address2 varchar2(512); -- Probably no error, but wastes space
  email_address3 varchar2(32000); -- No error, no wasted space

Once your variable is declared as 2000 characters or more, the variable is no longer statically allocated to accomodate its maximum size; instead, it's dynamically allocated every time it's assigned, based on the exact size that's really needed.

email_address2 := 'someone@example.com'; -- Rest of 512 characters are wasted empty space
email_address3 := 'someone_else@example.com'; -- Although declared with length 32000, only 24 characters are allocated

VARCHAR2s declared with huge lengths make great stocking stuffers, such as collection elements. You can make complex data structures that potentially can represent big documents (each line up to 32K), yet don't take up any more space than is really needed.

Now let's look at a complementary technique, where again we keep the allocated size under control.

When you want a variable to hold the contents of a table column, there is a simple way to match the right length:

declare
var1 my_table.my_column%type;

If my_table.my_column is declared VARCHAR2(600), then that's what var1 will be too. No worries about selecting a value from the table and getting a length error. If you run ALTER TABLE to change the length of the column, next time your code is run, it will pick up that change automatically and adjust to the new length.

Maybe your selects are not quite so simple. Say you want to select col1 || col2 and store the results in a variable. How...?

declare
cursor my_cursor is select col1 || col2 concatenated from my_table;
var2 my_cursor%rowtype;

We let Oracle figure out the maximum length of the concatenated columns, then piggyback on that result to make a variable of the same type. (Actually, it's a record variable that has a field of the same type; to refer to the field, we'd specify var2.concatenated.) Again, we're safe from ALTER TABLE.

---

Addendum

Eddie Awad linked to this post from his blog, and I see that post attracted some comments that I should address here.

Looks like, in 10g, the limit has been raised from 2000 characters to 4000 characters. I like it when PL/SQL limits align with SQL limits, and the number 4000 is significant because that's the biggest VARCHAR2 you can stuff in a table. So you don't have to worry about declaring a variable of type TABLE_NAME.VARCHAR2_COLUMN%TYPE and having this kind of dynamic allocation happen or not depending on the size of the column.

Anyway, I'll just reiterate my advice from the PL/SQL Guide: don't try to finesse the VARCHAR2 length depending on knowledge of the exact limit; if you prefer dynamic allocation for a variable, use a huge length. I always use 32000. A PL/SQL guru I know uses 32500. (I haven't seen anyone use 3276(7|8), proof that nobody likes to tempt the gods of boundary conditions.)

Performance guru Jonathan Lewis discusses some detailed performance / scalability implications of using big or different-sized VARCHAR2 variables. The kind of code that would use huge VARCHAR2s, in my experience anyway, is not inserting into or querying those values from SQL tables; rather it's building data structures like the buffers used in HTP.P() or DBMS_OUTPUT.PUT_LINE(). So I'll keep that performance tip in mind for DML-intensive code, but not worry so much for code that mainly does string manipulation and displays the results as a web page via mod_plsql.

Quotable Quotes

Spare a kind thought for the hard-working yet underappreciated quotation mark. Single quotes or double quotes, you ask? Exactly!

If you're familiar with other languages such as Perl or Javascript, you probably intermix single and double quotes as needed. Double quotes around strings containing single quotes. Single quotes around Perl strings containing $ or @ characters, so they aren't interpolated as embedded variables.

With PL/SQL, you'll almost always use single quotes. The main exception is around identifier names, which you can enclose in double quotes if they contain special characters or if you need to force them into mixed case or lowercase:

execute immediate 'create table "My_Table" ...';

Without those double quotes, the new table would be known internally (in the USER_TABLES view, etc.) as MY_TABLE. The double quotes force the object name to be exactly as entered, meaning you would get an error from "select * from MY_TABLE" or "select * from my_table". (In this last case, the name my_table is translated silently to MY_TABLE.)

So, that's it then. Use single quotes for string literals, nothing more to learn.

var1 := 'Hello world!';

But what happens when we start dealing with real-world cases?

var2 := 'Oracle Database Administrator''s Guide';

Wait a second! What's with the 2 consecutive single quotes? That's the notation to escape a single quote inside a string literal. What a bother. If you want to generate a text file with a bunch of insert statements, you have to run a search/replace on each string to create 2 of each single quote. And you have to do it before wrapping single quotes around the data. When done in PL/SQL, this leads to one of my favourite PL/SQL statements of all time:

var3 := replace(var2,'''','''''');

We are only replacing 1 single quote with 2 single quotes, but between the surrounding quotes for the literals and the doubled quotes to escape them inside the literals, it looks like an entry in the Obfuscated C Contest.

Luckily, in Oracle 10g there is a way to avoid all the escaping. It still requires you to predict what characters your string might contain, but within that constraint it is much more readable:

var4 := q'{Oracle Database Administrator's Guide}';
var5 := q'[I'm the king of the world!]';
var6 := q'#Mommy's all right, Daddy's all right...#';

With the q literal notation, the first and last characters inside the quotes are not part of the string value, and single quotes are OK since you are making sure that the closing sequence (in the examples above, }', ]', and #') do not appear anywhere in the string. If the opening character after the q' is a "bracket"-style character, the matching end character is the corresponding close bracket. If the opening character is some other punctuation mark or what have you, the same character is used immediately before a single quote to end the literal.

Where this really comes into play is in writing out code of one kind or another from PL/SQL. In a web application, you might be writing out HTML full of double quotes, Javascript full of single quotes, or a combination:

obj.innerHTML = '<div style="float: right;">';

It's very comforting to know you can wrap the whole thing in a PL/SQL string literal without doing any replacements inside the string:

var7 := q'|obj.innerHTML = '<div style="float: right;">';|';

Again, you'll have to know based on your own coding conventions or a priori that the closing delimiter character doesn't appear immediately before a single quote.

I like to combine this technique with a SQL*Plus trick I mentioned in an earlier post:

var8 :=
q'|
@javascript_source.js;
|';

Now that Javascript is available to PL/SQL and can be plunked into a web page, concatenated with other source, searched/replaced, etc. It doesn't have to contain any escaped '' sequences and so can be reused with other languages, edited by a non-PL/SQL programmer, run through a Javascript validator, or anything else that strikes your fancy.

The Humble IF Statement

If I have one piece of wisdom to impart, it's this: pay attention to your IF statements!

Seems like the hardest part of knowing a dozen languages is keeping track of the different syntaxes for simple IF statements. (I took a tour of an Internet startup pre-dotcom crash, and saw my PL/SQL User's Guide open on a desk -- not to BULK COLLECT or the exception model, but to the page for the IF statement.)

PL/SQL is relatively verbose but straightforward: IF this THEN that END IF;

Python is a little more concise, leaving aside the line breaks and indentation:

if this:
  that
elif the_other:
  something_else

I could wish for a little more Perl-style forgiveness; I keep leaving "then" in by mistake.

My simultaneous favourite and least-favourite IF syntax is from Korn shell:

if [ this ]; then
  that;
fi;

"then" is its own insignificant statement, and so requires the semicolon after the condition to format the lines the intuitive way. The conditions are a weird mix of UNIX-style flags (-f "$filename" to check for file existence) and FORTRAN-style comparison operators ("$string1" eq "$string2").

I can never remember whether the semicolon is needed, or the details of the comparison operators, or sometimes even whether the condition is enclosed in [ ] or [[ ]]. Don't forget that spaces are needed after the [ and before the ], it's an error otherwise. So for me, ksh is the toughest language to switch back into on a moment's notice.

Now this post isn't just a language comparison. There are some shortcuts you can do with IF logic in PL/SQL.

If you find yourself writing statements like:

if x is not null then
  y := x;
else
  y := 'some default value';
end if;

...you can use the NVL() function instead:

y := nvl(x,'some default value');

Because this is a SQL function too, you can use it in queries the same way:

select name, course, nvl(grade,'Incomplete') grade from coursework;

For any row where GRADE is null, the value that comes back will be 'Incomplete'. Since we're plugging a function in instead of a column value, the trailing "grade" is helpful to give a name to the expression value, in case we wanted to wrap the query into a nested query or implicit FOR loop in PL/SQL.

The next level of IF is the CASE expression. This lets you replace a whole sequence of IF/THEN/ELSE clauses with a single expression:

letter := case
  when grade between 85 and 100 then 'A'
  when grade between 75 and 84 then 'B'
  else 'Kiss the honor roll goodbye'
end;

Old-time PL/SQL programmers might be in the habit of doing:

select nvl(something,something_else) into variable from dual;

as a way to do this kind of IF-NULL-THEN assignment for a single value. You can get rid of the query by using NVL in a regular PL/SQL assignment, or (my preference) turning the NVL into a CASE.

It's not the compactness that makes CASE a win for me, it's the fact that (as an expression) you can use it in the middle of some other statement where normally it would break up the flow of thought or require you to create dummy variables to hold intermediate values:

declare
  something varchar2(32000) :=
    case
      when release = 1 then 'value for release 1'
      when release = 2 then 'value for release 2'
      else 'some other value'
    end;

  procedure my_proc
  (
    param1 varchar2 := 'xyz',
    param2 varchar2 := 'abc',
    param3 number := case when to_char(sysdate,'Day') = 'Tuesday' then 7 else 2 end
  ) is ...

PL/SQL's CASE comes in 2 forms, the expression that you can plug in anywhere as shown above, and the statement that must stand alone and ends with END CASE instead of just END:

case
  when percent_full < 75 then
    perform_routine_maintenance();
  when percent_full between 75 and 99 then
    stop_nonessential_jobs();
    send_email_alert();
  when percent_full = 100 or cpu_too_high = true then
    panic_in_the_streets();
  else
    dbms_output.put_line('Some anomalous condition!');
end case;

The statement format lets you do multiple things in response to any condition, and you can test different variables in different WHEN clauses. You aren't just returning a value, but running full statements in each WHEN block. I don't use the statement format very often though. I find it is a little confusing to distinguish between the two forms (usually by checking whether the last part is END or END CASE) when reading existing source. And if you don't properly account for all possibilities in a CASE statement and execution falls off the end, you get a CASE_NOT_FOUND exception, so it requires extra care in constructing the conditions to always match one of the WHEN conditions. (You can trap the exception, but it can be a shock the first time you get one when you hadn't intended the CASE statement to cover every possibility.)