The Humble PL/SQL Dot

Like many other languages, PL/SQL has its own "dot notation". If we assume that most people can intuit or easily look up things like the syntax for '''IF/THEN/ELSIF''', that means that first-timer users might quickly run into dots and want to understand their significance.

The authoritative docs on the dots is in the Oracle Database 11g PL/SQL Language Reference, in particular Appendix B, How PL/SQL Resolves Identifier Names. As we can see from these index entries, the subject is mentioned here and there throughout the manual:

dot notation, 1.2.5.2, B.2
for collection methods, 5.10
for global variables, 4.3.8.3
for package contents, 10.5

When I was in charge of the PL/SQL docs, I rewrote that Appendix B to try and make it more helpful, to give more examples and state what kinds of problems you could avoid by knowing this information.

Today, as a PL/SQL programmer, I would go even farther in simplifying the conceptual information in plain English, and positioning the knowledge as important for troubleshooting. Something like...

Names that use PL/SQL dot notation can have many different meanings, such as a procedure inside a package, a column inside a table, or an object owned by another schema. In code that you write or inherit, you might use one of these idioms extensively. Which can make for a nasty surprise when your code stops working because someone creates a new table, schema, package, etc. with the same name as one of your dotted names. So, read that Appendix B to understand all the variations and the precedence rules.

Some additional tips I'll pass on where you can add or remove dots to get out of trouble:

If you are coming from Perl, where you use an expression like string1 . string2 to concatenate, the corresponding PL/SQL expression is string1 || string2.

When you want to refer to two items in different scopes that happen to have the same name, use dot notation for one of the references. For example, if your PL/SQL procedure accepts a parameter ID and then queries a table that has a column ID, the query won't work properly when you use a WHERE clause like WHERE id = id. Instead, write it as WHERE id = procedure_name.id.

If you are writing code for use with the PL/SQL web toolkit, you'll find the URLs are simpler and cleaner if you can keep dots out of them. That means using standalone procedures where you might normally use a packaged procedure, because the package notation would put the whole package.procedure name in the URL. You can get the best of both worlds by coding up the package as usual, then making a standalone procedure that simply called the packaged procedure.

The Humble PL/SQL Exception (Part 1a) - The Structure of Stored Subprograms

As I said in my previous post, The Humble PL/SQL Exception (Part 1) - The Disappearing RETURN, there are a lot of nuances surrounding exception handling. That post attracted some comments that I thought deserved a followup post rather than just another comment in response.

oraclenerd said (excerpted):

I'm going to have to disagree with you on the internal procedure (in the declaration section) paradigm. What about testing?
...
Now you have 2000 lines in your declaration section and 400 or so in the body. (I've seen it...really, I've seen it). Then you want to change one of those internal procedures...you can't test it without testing the entire thing.

This is to me one of the conundrums with any programming language; PL/SQL just has its own unique aspects.

Any set of procedures can be a testing challenge, since you can think of calling a procedure saying "do this", whereas you can think of calling a function as asking "tell me what would happen if you did this". Sure, it's easier to test a function, because you just have an in-memory return value that you can compare against some expected value, and don't have to worry about changing data by accident or doing rollbacks.

In the situations where I would use the technique of lifting code directly into internal procedures, the code is typically very short and easily verifiable: select count(*) into... followed by an IF test; a sequence of simple assignments that would be cumbersome to turn into one function per assignment; blocks of code that have already been verified in toto by virtue of tests run against the outermost procedure or function; that sort of thing.

After the code is modularized this way:

• The outermost procedure can often be improved and/or optimized simply by reordering the inner procedure calls. For example, to do all the "should I quit early" tests before doing any substantial work. Or to put "set up complicated string value" right next to "use that string value".


• Debugging steps such as removing blocks of code can be performed by commenting out single lines. (Much appreciated if those blocks of code themselves contain multi-line /* */ comment blocks.)


• If a logic problem does turn up in the procedure, it's easier to figure out the part to look at if it has its own descriptive name. And a tool like ctags makes it handy to jump directly to that inner procedure.

The problem with a 2000-line declaration section, to me, is no worse than if you go the package route and must fix syntax errors or track down logic errors within a big package body. I use my favorite SQL*Plus hack to push that code off into separate files once it gets too big.

Anyway, my point is not to advocate using this kind of structure for every procedure or even every big procedure, rather to suggest that if you do restructure a procedure this way, using exceptions instead of return can make the work a little simpler, which is not a bad starting point if you are trying to get your head around how the flow control works for exceptions.

---

Brian Tkatch said:

Personally, i have done this (with a PACKAGE though) by RETURNing a value from the PROCEDURE, and then checking it in the main code:

IF Some_check() = 1 THEN RETURN; END IF;

It's not as pretty, but i found it to workout nicely.

Sure, I'm a big fan of function-oriented design; I think it's been sadly overlooked in the rush to make everything object-oriented.

Restructuring the early tests into functions does take a little work. And it requires some design decisions -- use Boolean values, 0/1, or named constants? how to ensure all cases are handled, maybe use the case statement? OK, I made my function return Boolean values and tested the values inside a case statement; but I can't test the function values via SQL queries, and maybe case will throw a runtime exception if some unexpected value like null comes back. The sample code in Brian's comment doesn't suffer from those problems, but this is the kind of thing I could imagine a junior programmer coding too elaborately.

That's not to say that the resulting code is any better or worse, just that it's now subject to potential new bugs and maintenance (have to document return values etc.) by introducing functions. That's the idea behind the use of exceptions in my previous post, to make the restructured code 100% the same as the original, not 99 44/100ths %.

The Humble PL/SQL Exception (Part 1) - The Disappearing RETURN

Exception handling in PL/SQL is a big subject, with a lot of nuances. Still, you have to start somewhere. Let's take one simple use case for exceptions, and see if it leads to some thoughts about best practices. (Hopefully, this is not the last post in this particular series.)

One common pattern I find in PL/SQL procedures is a series of tests early on...

if not_supposed_to_even_be_here() then
 return;
end if;

if no_data_to_process() then
 return;
end if;

if no_parameters_passed() then
 print_basic_page();
 return;
end if;
...

In real life, these tests tend to use hardcoded constants, queries, etc. that clutter up the procedure and make it hard to follow, rather than descriptive names as in the example above. One simple solution is to move the whole block into its own inner procedure:

check_if_supposed_to_be_here();
check_theres_data_to_process();
check_parameters_were_passed();

These procedures, declared with the procedure ... is ... syntax immediately before the begin of the main procedure, can access all the variables from the main procedure, so they typically don't require parameters. In most cases, you can just lift a block of confusing code from the main procedure, and turn it into an inner procedure with a descriptive name.

However, the return statement complicates things. When transplanted into an inner procedure, it loses its mojo. Instead of cutting short the entire procedure, it becomes essentially a no-op, because now it's at the end of a short inner procedure that was about to return anyway, back to the middle of the main procedure. The solution is to use an exception, which requires structuring the whole business like so:

create or replace procedure big_procedure as
  num_rows number;
  exception skip_normal_processing;
  procedure check_data_to_process is
    select count(*) into num_rows from data_table;
    if num_rows = 0 then
      raise skip_normal_processing;
    end if;
  end;
begin
  check_data_to_process();
  ...do all the normal stuff if there really is data to process...
exception
  when skip_normal_processing then null;
end;
/

Now if you detect some condition that means the procedure should bail out, it really will. If you can anticipate that your procedures might get lengthy enough to benefit from using inner procedures this way, you can plan ahead by using exceptions right from the start, instead of starting with return statements and then turning them into exceptions when you restructure the original straight-line procedure.

One thing that still bothers me is the way the control flow jumps around. The calls to the inner procedures jump backwards, and if any "stop! now!" conditions are triggered, control jumps forward all the way to the end of the main procedure. When I visualize such a structure, it reminds me just a little of spaghetti code. I know that on paper, all is as it should be -- all the reusable / modular code is separated out at the front, all the error handling and termination code is separated out at the end. I just would like to see more real-life cases where such structure saves on maintenance and debugging time, before passing final judgment.

You've Got to Fight for Your Invoker's Rights

This post is about a PL/SQL feature that doesn't get enough respect, "invoker's rights".

First off, what's its real name? Depending on the source, you'll see the feature name spelled "invoker's rights", "invokers' rights", or "invoker rights". That makes a difference -- you'll get different results in Google depending on what combination of singular, plural, and possessive you use. And to be strictly correct, shouldn't you hyphenate the adjective form, that is, refer to things like "invoker's-rights subprograms"? I'm not even going to go there. Although I personally call the whole feature "invoker's rights" to agree with the PL/SQL manual, I'll try to make it through the rest of the post without using that phrase at all.

After all that, the syntax for the feature is AUTHID CURRENT_USER. Although there is an opposite AUTHID DEFINER clause, since that's the default, you would probably only ever use the CURRENT_USER form of the clause. It might get more love (and be easier to search for) if we called them "CURRENT_USER subprograms" or some such.

The mechanics of this feature are relatively easy to see. You can find the details in the PL/SQL manual, or get a tutorial that points out some of the nuances, or this Steven Feuerstein article with some best practices.

But still, how does that play out in the real world?

Well, you may have a PL/SQL application that goes through several versions, with each version in a different schema on the same database server -- MYAPPV1, MYAPPV2, MYAPPV3, etc. Or maybe there are slightly different incarnations of the app for different business groups. When you make a fix or improvement to one procedure or function, if that change is applicable for the older or alternate versions, you need to recompile the procedure or function in each schema. If program units that needed periodic upgrades were put into a central schema and declared with AUTHID CURRENT_USER, making the change in one place would propagate the improvements to all versions of the application. You could hardcode the central schema name in all calls to the CURRENT_USER subprograms, or create synonyms and pretend they're in the same schema as the rest of the code.

The trick then would be to identify which procedures and functions are the best candidates for this treatment. Logically, they should be small simple subprograms that have relatively few dependencies, so they won't break if your application gains or loses tables, columns, or other subprograms as it evolves. They should also be subprograms that you could predict would be important to fix or upgrade in the future -- ones that could give a big speedup when you learn some tuning technique or use some feature in the latest database release; ones that implement security checks that you'll make more stringent as security practices evolve; ones that display common UI elements that you can make more usable and accessible over time.

Of course, this type of foresight is easier said than done. Sure, just take all your slowest, buggiest subprograms with the worst output, and separate them out. But you might be able to retrofit such changes at a reasonable point. I'd suggest evaluating whether you could make use of AUTHID CURRENT_USER around the time of the 3rd instance or version of the application on the same server.

The Humble COUNT( ) Function

Here's another ode to a small but fundamental aspect of Oracle, following the same theme as The Humble IF Statement. This time, let's look at the COUNT( ) function. I think when you look at it the right way, it opens up the whole story about database performance.

What's the first thing you do when poking around an unfamiliar system? I'll bet it involves SELECT COUNT(*) queries in one way or another. SELECT COUNT(*) FROM USER_OBJECTS to see how much "stuff" is in the schema. SELECT COUNT(*) FROM data_table to see how much data is around. SELECT COUNT(*) FROM audit_table to see how much usage the application gets. You can run SELECT COUNT(*) on various other data dictionary views, and with some WHERE clauses on regular tables, to answer questions like "how many...?", "are there any...?", and "confirm there are no...".

You can also do SELECT COUNT(column_name) FROM table_name. That brings back a count of the number of rows that have non-null values in that column. What's that for?

1. It demonstrates that you at least know something about the table, the name of a column. That's a small stake in the ground when you're establishing your credibility with experienced DBAs and developers.


2. It suggests that you'll probably want to use that column in a WHERE clause test, later in a bigger query. If you use that column in an =, >=, BETWEEN, LIKE, etc. test, that comparison can only match non-null values. COUNT(column_name) represents an upper limit on the rows that could come back from such a query.


3. It demonstrates that you're thinking on a higher plane. Not "how many physical rows total in this table", rather "how many significant rows with data in them". If you're a DBA trying to reclaim space, COUNT(*) might make more sense. If you're a developer tuning query performance, COUNT(column_name) is probably the right one to use.


4. The COUNT( ) query has a better chance of using an index if you put in a column name rather than *. COUNT(*) can take a long long time on tables with millions of rows.

Let's think about that last point. A typical index doesn't represent null values. The default index is a tree data structure (a B-tree) that would be right at home in a Java programming course. You could have a table with 50 million rows, but if only 1 million rows had something filled in for column X, you could build an index on that column and the index would take a relatively small amount of space -- disk space to store the index, and memory to manipulate pointers to the real data. Any query that tested that column could immediately ignore the other 49 million rows as irrelevant -- and that's why the index doesn't try to record which rows have nulls.

That means if we had a kind of index that indexed nulls, or one that guaranteed there weren't any nulls in the table, COUNT(*) could run really fast. What do you know, we do have indexes like that. Bitmap indexes represent a string of zeros and ones for every different value in a column, including null, to say very quickly whether a row contains a particular value. So when you run COUNT(*) on a table with a bitmap index, it could just count the number of zeros and ones for some value from that column. And a NOT NULL constraint on a column, which also comes for free with a primary key, means COUNT(*) could just count the values in the index, knowing that all the rows have some value for that column, so the number of values in the index matches the number of rows in the table.

That leads us to a thought experiment that's also practical. In SQL*Plus, you can issue SET AUTOTRACE ONto show the explain plan for every SQL query you run. If you want to get a preview of query performance, you can turn on autotrace, wander around your database doing COUNT( ) queries to see how many rows would be returned by your real queries, without the overhead of actually bringing back all the data.

If you do some tests this way, you'll quickly see the results in the explain plans. Index range scans, fast full index scans, and other things with the word "index" in them are generally cause for happiness. Full table scans are generally cause to look closer at the query; for small tables they're not so bad, for big tables they're often a warning signal.

Comparing COUNT(*) and COUNT(column_name) this way, you'll see illustrations of what I've said above. COUNT(indexed_column) produces a happy explain plan. If the table has any bitmap indexes, a primary key, or a NOT NULL constraint, COUNT(*) will take advantage of that fact and also use an efficient explain plan.

That said, sometimes you have a table with, say, 35 millions rows and a primary key, and COUNT(*) still takes a long time to come back. What's up? Well, an index with 35 million entries will take a lot of I/O to read and scan through. That's the basis of all the histograms, cardinality, and other arithmetic that revolves around database performance. (Like "don't use bitmap indexes if the number of different values exceeds 20% of the total number of rows", or "don't use bitmap indexes for small sets of values like Male/Female, where the rows with different values are all mixed together".) Sometimes the theoretical speedup from reading the fancy data structures and dereferencing pointers to get to the disk data, is outweighed by the practical disk and memory considerations to go through the index. Maybe the explain plan for a COUNT( ) query will be different from the same query bringing back real data, for that reason. And that's why there are different levels of expertise when it comes to database performance and query tuning.

Now, I'm just a guy who took the SQL Tuning course, read a lot of Oracle Concepts and Performance docs long before having any practical use for them, more than once drummed his fingers waiting for COUNT(*) to come back with an answer, and in a previous life wrote about close-to-the-metal performance considerations for IBM's POWER and PowerPC chips. This post doubtless doesn't capture all the nuances of query performance. This is just the way I think about that simple little COUNT( ) function.

Don't Fence Me In: All About Constraints

Constraints are simultaneously one of my most favorite and least favorite Oracle Database features. They're great for keeping bad data out of the database. They're a terrible imposition on object-oriented, agile, or <insert your favorite buzzword here> coding style. They save a ton of repetitive coding, writing the same logic in different languages. Hey, we already wrote all that redundant code in 10 languages and we'd hate for all those weeks of debugging to be wasted. Etc. etc.

If you're coming from a Java background, you might think of constraints like a straitjacket that forces your exception handling code to be organized a certain way. But that's the wrong way to think of it. Robust database code must TRY/CATCH for unexpected conditions anyway, in case the power goes out or the hard drive fills up just as you ask to do a completely innocuous INSERT or UPDATE. What if some maverick team member fires up SQL*Plus to make bulk updates to one table, bypassing all the Java application code and its error checking, without understanding the conventions that everyone agreed on for the data values, or the corresponding changes that must be made in other tables? The exception from a constraint is just another error that you expect never to happen, but you don't rely on it not happening.

If you're coming from a SQL, PL/SQL, or maybe more of a scripting background, or if your team programs in different languages or consists of fallible human beings, you can think of constraints like your very own bridge-keeper from "Monty Python and the Holy Grail". Whoever would insert or update table T must answer me these questions three, else an exception raised shall be.

For example, on a command like INSERT INTO GRAIL_KNIGHTS SELECT * FROM ROUND_TABLE_KNIGHTS...

• If Sir Lancelot and Sir Robin both give the same answers, a UNIQUE constraint will catapult Sir Robin into the Gorge of Eternal Peril.
  
• If Sir Galahad can't name a favorite color, a NOT NULL constraint will send him to the same fate.
  
• If King Arthur gives the wrong airspeed velocity of an unladen swallow, a CHECK constraint will spell his end.
  

As a bonus...

• If you don't count correctly when intoning the ritual, the Holy Hand Grenade of Antioch won't work because of its PRIMARY KEY constraint. (It's probably best to count to 3 via a sequence, and not write a psalm about that special value. In the database world, the ritual is different every time.)
  
• If you are a French knight and receive a gift of a Trojan Rabbit, a FOREIGN KEY constraint will keep it out of your fort.
  

When I first joined Oracle, almost the first thing I had to do was document constraints in 8i, in the Application Developer's Guide - Fundamentals. That was a frustrating experience for me as an Oracle newbie. There was information about "benefits of constraints" -- but who exactly were we trying to convince, or was that just marketing fluff? You could set up constraints with CREATE TABLE or ALTER TABLE -- why one or the other? Who exactly would be issuing all these DDL statements to work with constraints, a developer or a DBA? Here was technology leaps and bounds ahead of the competition, but as a result, there was maybe less depth of understanding about how that feature would be used in the real world.

Years later, now I (think I) know some of the answers, but it's too late to rearrange and rewrite the documentation to my whims. (I make suggestions with the reader comment forms in the 11g library, same as anyone else.)

The "benefits" information is aimed at the crowd who like to put all their error-checking logic in the middle tier. If anything, I'd like to have more simple examples to extol the benefits, particularly for scenarios involving Java client code, to get a feel for best practices for exception handling.

The CREATE TABLE syntax is used by the experienced enterprise types who plan everything out in advance. The ALTER TABLE syntax is used by those who came around to the idea of constraints after their coding was already underway, or who want to experiment before deciding. (I call this the "try it and see" style of development.) I'd like to be able to bridge from one style to the other: here is the ALTER TABLE syntax, and if you decide this is a worthy change, here's the equivalent CREATE TABLE syntax to make in your installation script.

There's at least one counterintuitive case, where careful advance planning requires you to use ALTER TABLE instead of CREATE TABLE. If you want to periodically turn off constraint checking for a primary key, you must create the table without the primary key clause, create an index on the primary key column, and do an ALTER TABLE to add the primary key. The primary key will then use your index, which must not be a UNIQUE index so that later you can "defer" the primary key checking.

The extra keywords in the CREATE TABLE / ALTER TABLE syntax for constraints indicate how dogmatic you are about keeping bad data out of your tables. DEFERRABLE means you may some time in the future allow bad data in the table for a brief period, but you're confident you can fix any problems before committing. INITIALLY DEFERRED means you expect to do this cleanup the very first time you insert data, or that the table already contains bad data and you'll clean it up immediately after creating the constraint. NOVALIDATE means you don't want to test the data that's already in the table -- either you're really confident that it's good, or you don't care if there are problems -- but you do want to check future data that's inserted or updated.

Once upon a time, you would do ALTER SESSION SET CONSTRAINTS = DEFERRED. These days, you could instead use SQL, as in SET CONSTRAINTS ALL DEFERRED or SET CONSTRAINT MY_TABLE_FK DEFERRED.

When/why would you use all of those keywords? I guess it boils down to a combination of convenience and performance. Consider a development scenario, where you insert personal data that has minor problems with the data, perhaps bad capitalization in some of the names. You could run a fixer function as the data goes into the table, either by calling the function in the INSERT statement, coding it into a SQL*Plus datafile, or by setting up a trigger. But (a) each of those is less convenient than just loading the data and then running 1-2 UPDATE statements afterward, and (b) maybe you want to start crunching numbers that don't rely on the textual columns ASAP, so you don't want the cleanup processing to slow down the initial data load.

My favorite analogy here is to XML processing. We've all been given XML documents that were invalid for some ticky-tacky reason... an unquoted attribute like width=100, or an uppercase tag name like <P> in an XHTML document that requires lowercase tags. It's cumbersome to do anything with those documents as XML, the "constraint checking" is so tightly integrated into the parsers. Oracle SQL lets you choose how "constraining" such easily detected/fixed data inconsistencies should be -- fatal error, try again, or don't check until I've cleaned up the data.

The ability to rename constraints is another offshoot of the "try it and see" development methodology. If you create a constraint without giving it a name, it might be years before bit rot sets in and somebody actually tries to insert bad data. At that point, the error message will cite some unintelligible name like SYS_C000123. After some detective work querying the USER_/DBA_/ALL_CONSTRAINTS views, you'll track down that this name refers to some primary key, check constraint, or what have you, and you'll use the ALTER TABLE ... RENAME CONSTRAINT syntax to give the constraint a descriptive name, so future error messages will be easier to understand. Again, I haven't found a clear path in the documentation that leads from troubleshooting examples of ALTER TABLE syntax, back to the equivalent CREATE TABLE examples or syntax so you can plan ahead next time.

As to who actually needs the information about constraints, that's still fuzzy for me. The world is full of application developers who don't take enough advantage of stored procedures, triggers, and constraints. It's also full of DBAs who aren't allowed to poke around with business logic in application code. So anywhere the constraint best practices are documented, it never feels to me like they are reaching quite the right receptive and empowered audience. There's no "Oracle Database Jack-of-All-Trades User's Guide" for the person who fills both developer and DBA roles.

Further reading:

• My original shot at documenting constraints, in 8i.
  
• In 11g, there is a chapter in Concepts, a chapter in the Advanced Application Developer's Guide, some corresponding SQL Developer shortcuts in the 2 Day Developer's Guide, a chapter in the Administrator's Guide, a chapter in the Data Warehousing Guide, and bits and pieces here and there about performance implications.
  
• Tim Hall's article from 8i about constraints covers the different combinations of keywords like DEFERRED and NOVALIDATE.
  
• Renaming constraints.
  
• SET CONSTRAINT[S] syntax.
  

Thoughts on Debugging PL/SQL Web Applications

At OOW, I ran into Stephen Feuerstein after seeing him demonstrate Quest Software's "Quest Code Tester" product. Considering how I might use a product like that for testing web-based applications, I suggested a couple of enhancements.

The biggest, most important procedures that I test in PL/SQL are those that generate entire web pages. For that kind of testing, you can't look at whether data has been changed in a table, you have to look at the HTML output of the procedure. In a testing scenario, that output would be in the internal buffer used by the HTP package and the others in the PL/SQL web toolkit.

An important procedure could generate a big web page. For that reason, I'd like to be able to compare at a finer granularity than whether the generated web page matches exactly some version that was stored for testing purposes. I think the ideal technique would be to run a regular expression test over each line of output, and be able to check "does any part of the page match this pattern?". It's that kind of flexibility that's missing in a lot of test environments, e.g. causing anxiety over the prospect of changing some text in an error message it might break some text case that does an exact match on all output, not just looking for the error number.

The contents of a web page could be unpredictable. For example, a page of search results might not be exactly the same after the search index has been refreshed. And Web 2.0-style pages could have random elements like "Tip of the Day" or a list of online friends, Twitter messages, or some set of recently viewed links. Even just personalized text like "Hello John".

In testing, I would like to ignore all those things and just focus on the parts that vary according to the parameters. For example, in a search of Oracle documentation, if the search term is "oracle", I expect that somewhere on the page will be a "Next>>" link. If I pass in the right parameters to retrieve page 1 of results, I expect that nowhere on the page will be a "<<Previous" link. If I pass in a nonsensical search term, I expect that the page will contain a particular message saying there aren't any results. For intentional misspellings, I might want to confirm that the right "Did you mean?" message comes up.

In addition, I might want to test certain invisible properties of the page, like the attributes of meta tags, links to stylesheets, or instead of showing exceptions to the user I'll catch them but embed little coded messages inside HTML comment tags.

Now, of course, someone could write a set of test cases in Perl or Python, even shell scripts, to retrieve URLs using some combination of parameters, then do this scanning of the content. But a PL/SQL-aware tool could be more convenient by hooking into the data dictionary to see the available parameters and the procedure source, which is why I'm intrigued by the Code Tester product.

Of course, any big procedure is composed of little parts. It's those little parts that do things like returning a string in a certain format, computing what number to display on a page, opening a cursor for a query. Those procedures and functions are the easy ones to unit test in an automated way, which is what the demo focused on. If you pass this number in, do you get this number out? If you pass zero, a negative value, a too-high number, do you get back the appropriate result or the expected exception? And so on for strings, collections, and other datatypes.

The wrinkle I throw into that testing scenario is internal procedures and functions. If something is directly callable, it'll be a top-level procedure or function, or inside a package. If it's reusable, chances are high it'll be in a package. But if something is abstracted purely so that my procedure body can be written like pseucode:

prepare_for_action();
run_query();
while results_left_to_process() loop
  process_results();
end loop;

then I'll nest those procedures and functions inside the current one. No danger of them being called by accident (or by a malicious attacker, in a web scenario with mod_plsql). No name conflict if I want to use the same step name in a different procedure. They can access all the variables from the current procedure, so I don't need to load down the calls with lots of parameters.

Automated testing for those internal procedures and functions could be tricky. They can't be called directly from unit tests outside the original procedure. Stephen suggested using conditional compilation. The first idea that jumped to my mind is to generate instrumentation code and use some SQL*Plus hackery to embed it in the main procedure:

procedure test_me is
  procedure step1 is...
  procedure step2 is...
  function func1...
-- This source file could be generated by a testing tool.
-- But using @ halfway through a procedure is a SQL*Plus-ism
-- that's doubtless unsupported.
@unit_tests_for_test_me;
begin
-- Conditionally compile this block under non-testing circumstances...
  step1();
  step2();
-- Conditionally compile this block under testing circumstances...
  run_unit_tests();
end;

How PL/SQL Could Be More Like Python

In previous posts, I suggested a couple of syntactic or thematic items from Perl and PHP that I thought would make PL/SQL friendlier and more accessible. Hey, maybe Python has some things too!

One thing to like about Python is the way its composite data structures build on top of each other. A list or a tuple can hold a bunch of numbers or strings in an array-like structure, but those things can also hold heterogeneous data items like an assortment of numbers, strings, other lists, other tuples, etc.

Another powerful idea is that a composite data structure can be used as the lookup key for a hash table / associative array / Python dictionary.

Finally, complex data structures with different numbers of elements can be passed as parameters or function return values, without the need to declare explicit types.

Underlying all this functionality, at least from a user's perspective, is that these complex data structures can be represented nicely as strings. Here's a little interactive Python output showing some list and tuple values. The >>> lines are what I entered, the other lines are how Python mangles the escape and quote characters for output.

>>> ['i do not care']
['i do not care']
>>> ['i don\'t care']
["i don't care"]
>>> ['i don\'t care about "that"']
['i don\'t care about "that"']
>>> ('i','don\'t','care')
('i', "don't", 'care')
>>> [ 1, 2, ("a", "b", ["x", 3] ) ]
[1, 2, ('a', 'b', ['x', 3])]

So we can see that there is a fairly well-defined way of making these data types into strings.

PL/SQL gained a lot of power when it got the ability to use string values as keys for INDEX-BY tables, which soon became known as associative arrays to be more familiar to people who used such things in other languages. It would be even more powerful if you could use VARRAYs, nested tables, etc. as lookup keys. You could solve problems that require a higher order of thinking than just "have I already encountered this integer" or "I will assign the symbolic name 'XYZ' to such-and-such a DATE value". You could say, "I know my web site is under attack when the counter corresponding to this combination of IP address, URL, and time interval is greater than 100".

PL/SQL can express something like this already, using nested collections. But it requires a lot of type definitions, which aren't readily reusable across different procedures without a lot of advance planning. There is a point where foresight crosses the threshold into rigidity. When dealing with in-memory data structures, I like the flexible approach of Python.

I don't think this kind of processing really requires much, if any, change in PL/SQL itself. All you would need are functions that could turn a VARRAY, nested table, etc. into a single string as in the examples above, so the string could be used as a key into an associative array. And the contents of the associative array element could be another string that represented a tuple or other data type that isn't directly supported by PL/SQL.

With a set of functions to go back and forth between strings and built-in types, you could emulate some features that are limited now by PL/SQL's type strictness. For example, it's difficult to write a function that accepts a VARRAY with 5 elements, and returns a modified VARRAY with 4, or 6, or 3 elements. So certain classes of recursive operations are not practical.

If the data structures were all passed around as strings, and unpacked into associative arrays behind the scenes, that would be powerful but slow. Perhaps the best compromise is to use a small number of uniform associative array types at the top level:

type python_dictionary_t is table of varchar2(32767) index by varchar2(32767);
type python_list_t is table of varchar2(32767) index by pls_integer;

Each string element could encode a single value, a list, a tuple, a dictionary, or whatever. But the basic datum being passed around could be one of these user-defined types.

Notice that a list in this scheme has numeric indices, yet isn't fixed size like a VARRAY. You could pass to a function a "list" of 4 elements with indices 0..3, and get back another "list" of 5, 1, 10, or whatever elements. There's nothing intrinsically forcing the index values to be sequential, that would have to be enforced behind the scenes.

Imagining how all this could be coded up inside the existing PL/SQL language, it occurs to me that the part that would be a real performance roadblock would be reassigning all the numeric index values when doing list operations like pop, push, etc. Element 0 becomes element 1, element 1 becomes element 2, element N becomes element N+1, or vice versa. So perhaps the biggest change needed to PL/SQL itself is a fast way of changing the key for an associative array element, without actually copying the element itself under a new key. Something like:

element.key(i) := j;

There could be a performance or memory hit when stringifying entire complex datatypes to use as keys for hash tables, if every complex value was passed through TO_CHAR() while evaluating it as a key. Perhaps the right approach is another built-in function like TO_KEY(), which could take something like a varray of gigantic VARCHAR2s, or an object type with member functions, and create a composite key with a bunch of MD5 or similar checksums, instead of the entire source text of the strings or member functions.

How PL/SQL Could Be More Like PHP

This is really a trick subject line -- PL/SQL already has the capability to be very much like PHP. PL/SQL Server Pages lets you write template HTML files including PL/SQL parameters, expressions, and blocks, and turn them into stored procedures that produce HTML output using HTP.P() procedure calls. The angle-bracket syntax around the expressions and blocks is very similar to that of PHP. And a PL/SQL coder has a headstart when it comes to optimizing DB performance and avoiding security problems.

PL/SQL Server Pages was the first web-related feature that I documented at Oracle. When I started using it myself, eating my own dogfood so to speak, that's when the potential functionality enhancements sprang to mind.

PHP is so popular because it's all based around text files, you can modify your source with any kind of HTML tool, and you don't have to do anything special to get the code to run, just put the files in the right places.

PL/SQL Server pages are also based around text files, you can also run them through HTML Tidy, and edit them in DreamWeaver or what have you. However, to use them requires the loadpsp command. With this command, you specify the user ID and password on the command line. That's not the ideal mode of loading code if you normally sit at the SQL*Plus command prompt, already logged in, and load new/changed stored procedures via '@script_name'.

With PHP, the way to load code into different parts of the application structure is to put the files in different subdirectories. The equivalent for PL/SQL Server Pages would be to use a template file to generate a packaged subprogram, an internal procedure, or a method for an object type. But, to my knowledge, loadpsp can only create a top-level procedure, which best practice says you should keep to a minimum.

Now, all of this could be worked around if loadpsp could generate the source code for the procedure and stop there, leaving the file behind instead of loading it into the database. You could set up a makefile to transform each .psp file into a .sql file. You could load the .sql files into the database from inside SQL*Plus without doing a separate login each time. You could set up a script using a SQL*Plus HOST command to go through both steps. Perhaps that would incite the SQL*Plus dev team to create a new directive similar to '@' that would run loadpsp and then '@' the resulting output file. If the output file could start with 'PROCEDURE ... IS', leaving off the initial 'CREATE OR REPLACE', you could use my favorite trick of:

package body foo is
@procedure1;
@procedure2;
...

to load PSP-generated pages as packaged procedures or object methods.

I never quite bought into the idea that with PHP, coders and designers would happily edit the same files, passing them back and forth and limiting their edits to either the HTML tags or the code blocks depending on their role. In my experience, every expert tends to have ingrained assumptions that don't work well with top-down planning. The designer tasked with making a more attractive-looking page will go off and write HTML that's structured very differently from the existing page, that PL/SQL blocks and widgets can't be neatly dropped into. The PL/SQL coder tasked with adding some dynamic elements to the page will chafe at working inside markup produced by some HTML authoring tool they disdain; sometimes, it's too painful even to read past the DOCTYPE tag. :-)

How PL/SQL Could Be More Like Perl

An idea has been rattling around in my head for a while now. I know, I know, there's a lot of empty space so it makes a lot of noise. :-) Could PL/SQL become more popular and easier to use by taking a couple of points from the Perl playbook?

I read a blog post about how scripting languages are or aren't gaining on Java. I think supporting curly-brace syntax would be a nifty way to encourage PL/SQL adoption among both scripters and Java coders. But maybe that's just me.

My first impressions of Ada and then PL/SQL were somewhat negative: hey, this verbose BEGIN/END, IF...THEN...END IF syntax reminds me of Pascal. I first programmed in Pascal on the Commodore 64, and it was deadly slow to compile and run, owing to the whole UCSD P-Code business. Then at university, it was the first language for coursework, and even though it ran really fast on UNIX boxes, I always felt that behind the scenes, more deadly slowness was occurring.

These days, 95% of the languages I code in use curly braces for blocks:

if ... { ... }
function ... { ... }
for ... { ... }

Imagine if PL/SQL did the same. Suddenly, brace matching in vi/vim would work. (Hit % while cursor is on a brace character to find the matching brace at the other end of the block.) It would be unlikely to introduce logic errors; in my experience, mis-nesting of blocks is not a frequent source of errors in Perl or Java, and in deeply nested PL/SQL, even today a careless coder can just add END IF statements until the syntax error goes away. I sometimes write little comments after the END IF, END LOOP, etc. to remind myself of which block each one closes.

On the documentation end, the PL/SQL User's Guide and Reference, aka PL/SQL Language Reference in 11g, has never devoted reference topics specifically to "BEGIN Statement", "END Statement", "END IF Statement", etc. Instead there are generic topics like "Block Declaration", or a section on "LOOP Statements" that doesn't split out the "END..." part as a separate item. That's always made it hard for me to write auto-linking pretty printers for PL/SQL (i.e. no "BEGIN" topic to link from the "BEGIN" keyword), but it would make syntax enhancements slot right into the docs.

From a parsing standpoint, I think it's pretty straightforward. If you leave out a THEN or put an END IF where an END LOOP is expected, PL/SQL gives you a straightforward error message. It knows what block is open and what the expected closing symbol is. So I imagine that it's practical to parse syntax like:

procedure foo is
x number;
{
  if 1 > 0
  {
    for bar in (select * from t1)
    {
      x := 3.1;
      declare
        x varchar2(64);
      {
        x := 'hello';
      }
    }
  }
}

{ takes the place of THEN, LOOP, and BEGIN. If a { is seen where one of those tokens is expected, treat it like the equivalent block beginning keyword. When a } is seen, treat it like whatever block closing keyword is valid at that spot. When a { is seen with nothing special preceding it, treat it like a nested BEGIN.

When I think about it more deeply, I see potential problems around ELSE and WHEN clauses, where those keywords act as both the end of one block and the beginning of another, but anyway this whole idea is probably just a fantasy on my part.

One other Perlism that really struck home lately. Perl lets you get away with a comma after the last item within an array-style declaration. This seems like a minor point, but when writing programs that generate other programs (or anyway JSON-style data structures), it is sometimes difficult to give special treatment to the first or last data item. For example, with SQL or PL/SQL it's simple to write out 1000 INSERT statements, each ending with a semicolon. But let's say you have an XSLT transform with a FOR-EACH loop that produces an initialization statement for a data structure. The output goes

  'item1',
  'item2',
  'item3',
  ...
  'itemN',

and then you're left with that final trailing comma that you need to get rid of somehow. I'll often put the comma at the beginning of each line for just that reason, and to allow easier reordering of parameters and such. But then the first item is a special case. Think of how many times you have to introduce a Boolean variable to make the output slightly different for the first or last pass through a loop.

I used to think Perl's optional no-op trailing comma was just a flippant design choice, but now I think it was a very shrewd way to make dynamic programming easier. One that some other languages could emulate.

Doubleplusgood vi Shortcut for PL/SQL Coders

One thing that bugs me (no pun intended) about PL/SQL syntax is the lack of the ++ and similar operators. If you use long variable names, you're penalized by having to type them twice in a simple increment operation:

rows_processed := rows_processed + 1;

I use vim, a cross-platform vi clone, for editing PL/SQL code. vim has a lot of extensions that the old-school vi user might never realize. One such is Ctrl-P when in insert mode. It looks backward ("previous") for the last matching word, and fills it in. For example, you could write the line above by entering:

rows_processed := <Ctrl-P>

vim fills in the variable name again, then you continue entering "+ 1;".

If the variable name you want is not the very last word, you can enter one or more letters first and vim will fill in the last word that starts with that prefix. For example:

toggle_switch := initial_value - t<Ctrl-P>;

produces:

toggle_switch := initial_value - toggle_switch;

The matching isn't limited to the current line, so you could fill in variable, procedure, etc. names from much earlier in the file.

Vim has a whole set of such extensions. Some of them like 'ab' are familiar from the original vi days, but you can boost your productivity by brushing up on the newer techniques.

A CASE of Functional Programming

Chris mentioned in response to my post on functional programming that the CASE construct is useful in such situations. You can use CASE expressions either in PL/SQL subprograms or in SQL queries.

I agree, although I'm not sure if my agreement is more because of developer convenience or the pragmatic (performance) aspect. When you use a CASE expression, you skip the step of putting the IF/THEN test into its own function, so a purist might say it's bad that you end up duplicating code if you use the same CASE in several different queries. However, I have found CASE to be faster than I expected in queries producing big result sets, so I use it sometimes in preference to setting up a real function and making a function-based index.

As Chris mentioned, CASE lets you turn a PL/SQL function into essentially a one-liner, just a RETURN statement that covers all the possible bases. CASE expressions force you to use the ELSE clause, so you're guaranteed to return a value no matter how strange the input arguments. [See correction/digression at end.]

In a SQL query, CASE can take the place of a function call, either for transformation of the output results:

select
  case region_code
    when 'NL' then 'Newfoundland'
    when 'ON' then 'Ontario'
    when 'CA' then 'California'
    else 'Some other place'
  end
from ...

or for a condition in the WHERE clause:

select ...
  where vacation_days > 
    case
      when emp_type = 'VP' then 10
      when years_service > 5 then 5
      else 2
    end
...

or (my favorite) in some clause where you wouldn't normally think of it, such as putting a dynamic ORDER BY clause into a query inside a PL/SQL procedure:

for item in (select ... where ...
  order by case
    when param = 'by_date' then date_added
    when param = 'by_name' then last_name
    else credit_card_number
  end
) 

Correction

As Boneist pointed out, my original assertion that CASE forces you to cover all possibilities is not correct for CASE expressions. For example:

select nvl(case when 1 = 0 then 'foo' end, 'bar') from dual;

returns 'bar', demonstrating that if the CASE expression "falls through" without matching any WHEN clauses, the result is null.

It is true for a CASE statement rather than an expression:

begin
  case
    when 1 = 0 then null;
  end case;
end;

That will throw an exception, because of the lack of an else clause. I can never remember whether that behavior is for the statement or the expression, because it's the opposite of what I would like. If I'm selecting actions, I'd like a default ELSE condition of "do nothing", so I could leave it out. But if I'm selecting values, I'd prefer to be forced to consider all cases.

I fretted about the CASE statement vs. expression difference when I first wrote it up in the PL/SQL User's Guide. Is there enough cross-linking so that people will see both forms? Is there too much cross-linking so that people will get the expression and the statement mixed up?

Also, do we really need to use a special term "searched case" for one variety of syntax? I suppose it is a long-standing term, since I see it used in SQL Server and MySQL contexts too. I just can't imagine going into a code review and arguing with someone that they should or shouldn't use "searched case", without having to define it and clarify whether that meant it just dives into the WHEN clauses, or starts with CASE something.

Anyway, knowing about the statement as opposed to the expression does have some point in the functional programming environment. Let's say you wanted to rewrite the COMPARE function from my earlier post using CASE, but if something went wrong, it should raise an exception rather than return a value. Instead of coding that as a single statement starting "RETURN CASE ...", you'd write:

function compare(x number, y number) return number is
begin
  case
    when x > 0 then return 1;
    when x < 0 then return -1;
    when x = 0 then return 0;
-- If there is some unexpected combination of parameters, NULLs or NaNs or
-- what have you, need to handle that case also.
    else raise some_package.weird_math_error;
  end case;
end;

A Word about Functional Programming

In all the back-and-forth about procedural programming and object-oriented programming, don't forget about another possibility: functional programming. It offers valuable lessons that can help with both hardcore SQL database operations, and the most modern and hip OO coding styles.

In some quarters, functional programming has a bit of a stigma from the Lisp days. In its purest form, functional programming does away with variables, turns even the most complicated program into a one-liner (with tons of brackets to nest properly), and has performance that varies depending on the characteristics of the language compiler.

These days, the introductory Comp Sci course at UC Berkeley (61A) uses Scheme as the teaching language. It's more accessible than Lisp, but still I found it more pleasant to follow along at home doing the exercises in Javascript.

On a pragmatic level, the upside of functional programming goes something like this. Once you write a function that can reliably read the color value for the pixel at coordinates (x,y), and another that can reliably write a color value for the pixel at (x,y), writing Photoshop just boils down to some data structures and a zillion calls to these functions. In a web development context, if you can make a popup window appear reliably for Firefox, Internet Explorer, Opera, Safari, etc., it doesn't really matter how unappealing the code underneath is, you can build more and more layers that call the same few functions. You can see the downside on many popular web sites; once people learned how to make ads appear and dance across pages, or pull code from other sites to track people's browsing behavior, they built on top of those functions without considering the ultimate performance hit or memory consumption to do all that for hundreds of browser pages/tabs at a time.

If you just want to introduce a bit of functional programming into your everyday life, you can do that without switching languages. Here are some ways you could use its precents to improve PL/SQL or other database code.

With functional programming, every program unit is a function (i.e. has a return value). This lets you map the outline of a program using essentially pseudo-code style. Instead of:

if x > y then x := 0; end if;

you would write something like:

if exceeds_limit() then reset_value(); end if;

When you've tried this before, you might have found it cumbersome to pass all the input values as parameters -- it's not any better to write is_greater_than(x,y) rather than x > y. But PL/SQL lets you write nested subprograms that access the variables of the parent subprogram, so you can make calls without parameters and let the subprograms get and set the values they need.

OK, it's true that reset_value() above is a procedure, not a function. But that's why I said full-on functional programming isn't strictly required. In traditional FP style, instead of doing assignments, you'd issue RETURN statements, and all the assignments would happen at outer layers of the program.

These pseudo-code outlines make it easier to implement bug-free versions of the named subprograms, since they're generally dead simple to write. You're also protected if you need to write several different variations of the main program, that call different combinations of subprograms or do things in a different order; you don't wind up duplicating all the code for the detailed logic.

Functional programming does instill a certain discipline to consider all the possible cases of execution:

function compare(x number, y number) return number is
begin
  if x > 0 then return 1; end if;
  if x < 0 then return -1; end if;
  if x = 0 then return 0; end if;
-- If there is some unexpected combination of parameters, NULLs or NaNs or
-- what have you, need to handle that case also.
  return null; -- Or raise an exception...
end;

When you're comfortable handling all the "halting conditions" for straightforward functions like that, it gives you confidence to tackle recursive calls. Typically, a recursive call will do a little bit of manipulation or testing to see if the function has enough information to return a final value, and if not, will call itself with a slightly modified (simpler) version of the input parameter. For example, N-1 for an integer parameter, or SUBSTR(param,2) for a string parameter. Something that trends towards zero, the empty string, the empty list, etc.

PL/SQL can do recursion, but its more complex data structures don't jump out as being ideally suited to recursive calls. For example, if you have a collection, it's not a simple matter to construct a new collection consisting of elements 2-N, to chop off the first one, or to "concatenate" collections to pass the results back through recursive calls. That's a common operation in doing parsing operations, and other scenarios often tackled through recursion. Perhaps with the enhancements to collection operations over the last couple of releases, recursive operations involving collections are now straightforward. If so, I'd love to hear from people who have done so.

SQL coders have been dealing with performance implications of functions for some time now, so they could swap war stories over beer with Lisp coders. If you aren't careful, a SQL query can make astronomical calls to expensive functions. But if you are careful, then you can be like one of those Lisp programmers who looks at any problem and says, "I could solve that in one line, since 1985!".

Oracle subselects and inline views are very good for doing "composition of functions", that is, producing a set of values and passing those values through a series of functions.

select line from
(
  select howmany || ': ' || label line from
  (
    select howmany, label from
    (
      select count(*) howmany, title label from t1
      group by title
    )
    order by howmany desc
  )
)
where rownum < 11
;

In the example above, we're producing a large number of values in the innermost section, and in the outer layers we're sorting the results, concatenating different fields into a single string, and finally limiting the number of results that come out at the end. Along the way, we're renaming some of the expressions and columns of the inner layers to be more descriptive and generic.

Performance-wise, it's best to push the most expensive operations and the limiting step as far inside as possible. If the innermost query only produces 10 rows, it doesn't matter how much transforming and sorting we do in the outer parts, because those steps only happen 10 times.

For example, consider these variations on the same idea:

select distinct(substr(lower(colname),2)) from t;
select distinct(substr(lower(distinct(colname)),2));

Let's say the table has 10,000 rows. The first query is relatively inefficient, because we're running LOWER 10,000 times, then running SUBSTR 10,000 times on the results, then finally doing DISTINCT which might prune the final results down to 1000. The second query runs DISTINCT right at the beginning (the innermost DISTINCT), which prunes the results let's say to 1500; then LOWER gets run 1500 times, and SUBSTR gets run 1500 times, and then the second DISTINCT brings the final results from 1500 rows down to 1000 rows.

I actually ran the first query above in a moment of Lisp-like "see, I can do this in one line", and it processed 22,000 rows in a fraction of a second. In a real business situation, the functions might be things like IS_GOOD_CREDIT_RISK() or DESERVES_A_RAISE_THIS_YEAR(), things that might even query other tables, and anyway take a long time to compute for every row. In which case it really is important to make the result set small in the inner layers, and save the expensive calls for the outer ones.

A whole class of Oracle features go into the idea of optimizing queries that call functions.

There is the function-based index, where the database pre-computes the results of a function for each row, and stores the results in the index rather than as a separate column. The function must be declared DETERMINISTIC, that is, it is guaranteed to give the same result every time when passed the same input parameters. (The database already knows that built-in functions like LOWER() and SUBSTR() are deterministic, so you can build function-based indexes that incorporate calls to those functions.) This approach can be better than having a separate column, because what if the logic breaks down and that column doesn't get updated when the other columns do? The function results will get out-of-date. If you only need the function results for use in WHERE clauses, the function-based index protects against that case. It actually protects a little too well in my experience, because when you recompile the function, the index becomes invalid and prevents DML operations on the table. So you need to separate out the code for that function so it doesn't get recompiled except when you really change it. Either that or add an ALTER INDEX...REBUILD statement after the CREATE OR REPLACE statement for the function.

In 11g, there is the function result cache, which devotes some memory to the results from functions. The idea being that if you have a function that translates 'CA' to 'California', 'IA' to 'Indiana', etc. the database should be smart enough to keep those 50 or so values cached in memory and skip the actual call to your function. That way, you could query large amounts of data without displaying cryptic verbatim column values, and without paying a performance penalty for the transformations. I haven't personally tried the result cache, but I'm itching to -- got those DETERMINISTIC keywords locked in and ready to go!

When you make a nested expression like regexp_replace(lower(substr(to_char(...)))), that's known as "composition of functions", and although that seems like a straightforward notion, there are things you can do with it. If you take that whole expression and make it into a function:

procedure transform_it(what varchar2)
return varchar2
deterministic
is
begin
  return regexp_replace(lower(etc. etc.

then you can reference this transformation from many queries; if you later realize there is a faster way to do it, you can rearrange the nesting and all those queries will speed up; and you can use a function-based index or the 11g function result cache to supercharge it.

Deleting Data, Part 2

In a previous post, I listed a few different techniques for deleting Oracle data. Just to recap:

DELETE statement in SQL (or PL/SQL).
FORALL loop construct wrapped around a DELETE statement in PL/SQL.
TRUNCATE TABLE statement.
Temporary tables.

Different themes emerge. Some ways are more flexible (DELETE statement), some are faster for large amounts of data (FORALL, TRUNCATE), and some are more convenient (temporary tables).

As I thought about it some more, I realized there must be 50 ways to... delete your data. Here are some of the others. I'm sure there are plenty more that I haven't had direct experience with.

ALTER TABLE DROP PARTITION falls in between a normal DELETE and a TRUNCATE. You've specified some criteria to divide up the values in a large table, for example by year or by area code, and with one statement you can get rid of all the data in one of those buckets. Perhaps every Jan. 1, you remove the data for the 5-years-ago year. Dropping a partition simplifies the behind-the-scenes bookkeeping for the physical storage and the indexes, as opposed to issuing a DELETE in a regular table.

If you add a foreign key constraint to a column, you can include the ON DELETE CASCADE clause. The foreign key references another table, meaning you can't insert data unless the value in that column matches one of the key values in a different table. ON DELETE CASCADE means that when data is deleted from the other table, the matching key values are deleted from your table also, saving you from having to issue multiple sets of DELETE statements for interrelated tables.

You can add a foreign key constraint via the CREATE TABLE statement or the ALTER TABLE statement. Here's where it becomes difficult to dream up different "tasks" for different ways to achieve the same end. Putting a foreign key constraint in a CREATE TABLE statement suggests you are planning ahead and have constructed a detailed database design. Putting a foreign key constraint in an ALTER TABLE statement could mean that you are troubleshooting a problem once the database is in operation; perhaps it was overoptimistic to assume that every programmer would remember to DELETE from all the right tables. Or it could just mean that you were writing a setup script, found it easier to guess the right constraint syntax for ALTER TABLE than for CREATE TABLE, and so wrote a simple vanilla CREATE TABLE statement immediately followed by one or more ALTER TABLEs.

Personally, I use foreign key constraints a little less than I might like. In a data warehousing situation, such as with a search engine, I might be fiddling with or reloading data to get everything just right, and want to avoid unexpected side-effects in other tables. For example, if you reload the set of US state abbreviations into a 50-row table, you wouldn't want all the customer data for California to disappear because the 'CA' row was removed for a moment.

Another space-saving technique that applies to PL/SQL only is the SERIALLY_REUSABLE pragma. It lets the database free up memory space used by package variables, more frequently than happens by default. Then you don't need to feel so guilty about having your package code load the entire contents of a table into a PL/SQL collection data structure. But the memory gets freed up so often that the pragma seems only useful for specialized situations with long-running sessions.

Let's broaden the objective a little bit. Some of these techniques make data disappear without requiring action on the programmer's part, keeping bad old data out of query results and freeing up space on disk. Taking off the DBA hat and putting on the developer one, perhaps we wouldn't care quite as much about the disk space. What about techniques to make data disappear from queries, even if it doesn't really disappear from the database?

The CREATE VIEW statement can create a rolling window based on dates or some other value in the data stream. For example, you might use a condition WHERE date_col >= SYSDATE - 7 to only examine the last 7 days worth of data. Because Oracle dates include a time component, that would mean everything from 1:25 PM last Saturday up to 1:25 PM this Saturday, using the example of my own clock as I type this. If you wanted only full-day periods, you would use a condition such as WHERE date_col BETWEEN TRUNC(SYSDATE) - 7 AND TRUNC(SYSDATE). That would include everything from the start of last Saturday, up to the start of today, and would give you consistent results if you ran the same query again during the same day.

Other views might restrict queries to the last 1000 web page views or financial transactions. I think of such views broadly under the "deleting data" umbrella, because just based on the passage of time or activity within an application, older data stops appearing in query results without any inconvenience to the programmer.

Another technique along the same lines is the CREATE MATERIALIZED VIEW statement. This has 2 distinct uses: either you have a table normally accessed through a database link that is so slow you just want to have a local copy that you can query, or you have such an expensive/complicated query against a big table that you want to keep the query results in their own table so you can retrieve or subset them much faster. The "deleting" part comes because the materialized view can be automatically refreshed, either on a schedule or whenever the data in the underlying table changes. So as with other views, you can sit tight and old data will stop showing up in your queries.

I've started making some headway with materialized views, but am not yet a master of the refreshing techniques. That seems to require perusing both the SQL Reference and the Data Warehousing Guide. All those Data Warehousing examples with quarterly sales figures etc. don't resonate much with me when I'm learning about MVs, analytic functions, or partitioned tables.

I think those are all the related techniques that I use or consider using for such purposes. Of course, you can always manufacture other situations or add layers on top of these techniques. You could set up a scheduled job that deleted old data, you could have a trigger that acted like a foreign key constraint by deleting from a related table -- the possibilities are endless!

Unlikely Combos: Deleting Data

One of the challenges I see in understanding how the Oracle Database works is when features for similar tasks don't fall into neatly organized categories. It's obvious that this feature should be documented over here, and that one over there, but where can you discuss them side-by-side? Maybe the features are from different functional areas, and it's tough to find a single reviewer who can verify. Maybe the feature is being used in ways not originally intended, which prevents a technique from getting officially endorsed.

Well, that's where a blog comes in. Personal opinions all the way!

I'll write some posts with features that I think of as addressing similar tasks, falling along a spectrum of convenience, capacity, and complexity. First up: deleting data.

The basic method for deleting data is the SQL DELETE statement. Well duh! You can delete one or a zillion rows in a table, or a subset of rows based on simple or complicated conditions. Everyone knows that.

delete from the_table;
delete from the_table where col_value > 10;
delete from the_table where col_value in (select some_other_col from some_other_table);

But as the volume of data grows or the conditions get more tangled, other techniques start to look attractive.

Next up is the PL/SQL FORALL statement. You can use this loop-like technique in situations where normally you would issue a sequence of regular DELETE statements, because it's too complicated to construct an IN list or what have you. You'd put the construct inside an anonymous block, stored procedure, or trigger.

forall i in 1..the_count
  delete from the_table
  where col_value = collection_element(i);

In real life, your WHERE condition would probably be more complicated, with different clauses referencing the i'th value of different collections.

forall i in 1..the_count
  delete from the_table
  where col_value between lower_bound(i) and upper_bound(i)
  and col_string not like '%' || string_pattern(i) || '%';

When I would normally write a little Perl script to spit out thousands of DELETE statements, instead I make the script generate an anonymous block that sets up one or more big PL/SQL collections, then runs a little FORALL-DELETE loop at the end. FORALL batches all the requests, so there's a lot less network traffic and other overhead. You can use the same technique to speed up large groups of related INSERT or UPDATE statements too.

Both DELETE and FORALL do all the nice Oracle behind-the-scenes bookkeeping that we all know and love. But sometimes, all you want is for a lot of data to be gone, ASAP. For example, in a data warehousing scenario, you might want to keep emptying a table and immediately re-filling it. That's where the TRUNCATE statement comes in. It skips some of the bookkeeping, so for example index statistics can go stale and the database doesn't reclaim space like it could. But it's fast.

truncate table the_table;

The opposite scenario is when you only have a little data to delete, but you want to delete it frequently. Maybe a stored procedure needs a little work area that isn't needed after the COMMIT, or after a web session finishes generating an HTML page and disconnects. That's when the temporary table comes in. It empties itself with no fuss, no muss, either when the session commits or disconnects, your choice.

Oracle temporary tables are faster to populate and clear than normal tables, although they don't totally avoid behind-the-scenes overhead. The key thing to remember, if you're a SQL Server expert, is that creating and dropping them is just as expensive as for a regular table, so you want to create a single temporary table as part of application setup, and let it live forever. Oracle takes care of deleting the data when you're through with it, and also keeps different sessions from seeing each other's data. So several sessions can be messing with the same temporary table at once, and each only sees the data it put there.

-- To clear the table as soon as the transaction ends...
create global temporary table t1 (scratch_value number) on commit delete rows;
-- To clear the table as soon as the session ends...
create global temporary table t2 (key_value varchar2(16)) on commit preserve rows;

OK, back to TRUNCATE. Sometimes, you want a lot of data to disappear quickly, but then the lack of bookkeeping bites you later. I've had tables where I truncated a couple of hundred thousand rows before loading new data, and then anything involving the new data was slow. I could go through the process of generating new stats, but then the whole operation takes as long as using DELETE for the whole table. Sometimes -- again usually in data warehousing situations where nobody else is using a table -- the practical thing to do is DROP TABLE followed by CREATE TABLE to recreate it.

Another kind of table that you can think of like a temporary table is the external table. The data comes from an outside text file, so you can imagine that the data ceases to exist (within the Oracle sphere of control, anyway) as soon as you finish querying it. It takes up space on the filesystem, but not in the normal tablespace and datafile regime. I haven't personally used external tables, but I've considered it for data that I thought of as "read once and forget".

Rikki Don't Lose That NUMBER

Everything you know is wrong. The more things change, the more they stay the same. Money makes the world go around. Let's see how many aphorisms I can spout while thinking about the Oracle NUMBER datatype.

The Oracle NUMBER datatype has a couple of attributes, precision and scale, that can be hard to grasp. The documentation spends some time explaining how these attributes affect the storage requirements and accuracy for numeric data. But what does it all mean, really?

Instead of storing integers in 16-, 32-, or 64-bit packages, or floating-point values in IEEE format, the Oracle database uses a different format that trades off some space and performance, to avoid rounding errors of the sort that would foul up financial calculations.

The precision has to do with how big your numbers could get, in terms of decimal digits rather than bits. Need to count something with values from 0 to 99? That's a precision of 2. Need to score tests from 0 to 100? That's a precision of 3; the 3 digits let you go up to 999, even if you don't use all those values.

When you declare a column type of INTEGER, again you're not saying you want values up to 32,767 or 2,147,483,647 like you might expect in binary terms. You're saying you want as many decimal digits as possible, which in the case of Oracle is 38 -- 99,999,999,999,999,999,999,999,999,999,999,999,999. INTEGER is just a synonym for NUMBER(38). If you don't really need that many, you can declare a smaller numeric type like NUMBER(10), and you won't have to buy as many hard drives.

OK, but banks and stores and stuff sometimes hand out these little sub-dollar units, coins I think they're called, or "change" as the kids say these days. No problem, that's the scale part. The "longest" number of decimal digits is given by the precision, and the scale just says how far over the decimal point should go, starting from the right. In terms of storage space in the database, the range 0-999 takes up the same amount as 0-9.99, it's just that the former is declared NUMBER(3,0) and the latter is NUMBER(3,2), meaning shift the decimal point 2 places from the end.

If you are a scientist, banker, or soap marketer, you probably have rules for how many decimal places of accuracy you need. That asteroid has only a 0.0000000062% probability of hitting the Earth, not a 0.00000001% chance like it said in the paper. We've made a breakthrough in purity and now it's 99.46%. Your savings account earned $000000.00 in interest this month. That sort of thing.

When I first heard that NUMBER had a special storage format, I thought Oracle had resurrected the idea of "binary coded decimal", which was used in old microcomputers like the Commodore 64. Instead of storing 8- or 16-bit integers, you could fit one decimal digit 0-9 in each nibble of memory, so you could get 0-99 in a byte instead of 0-255. The 6502 processor could do addition and subtraction in this mode. It bought you some speed doing things like implementing a scoreboard in a video game. When someone scored 15,850 points in a pinball game, instead of doing a lot of slow divide-by-10 operations to figure out what digits to display on the screen, you could use the BCD values as indexes in a lookup table to grab the bitmaps for "1", "5", and so on.

But no, the NUMBER storage format is different. More compact than BCD, but still slower than native machine operations. If all this business with decimal digits has no bearing on your results, that's what BINARY_FLOAT, BINARY_DOUBLE, and PLS_INTEGER are for -- raw speed. That last one, PLS_INTEGER, can't be stored in a table. It's only for doing arithmetic within PL/SQL, which can speed up your triggers and stored procedures, in case you were declaring loop indexes as INTEGER thinking that was like an "int" in C. These days, BINARY_INTEGER is a synonym for PLS_INTEGER, but it used to be slower, so I use PLS_INTEGER in case my code ever gets run on an older database release.

Most languages these days have done one thing or another to slow down the '80s style of pure machine arithmetic. In Java, for example, although you've still got the C-style int, many of the data structures require the type Java.lang.Integer, which is just an object containing an int. Manipulating these objects requires extra levels of indirection behind the scenes before you can actually add, subtract, etc. them.

I say the '80s were the int's heyday because everyone was going to so much trouble to avoid any kind of floating-point arithmetic. Want to draw a line? Use Bresenham's line algorithm. Want to draw a circle? Use Bresenham's circle algorithm. Bezier curves?!

OK, the Bresenham algorithms are from the '60s, but it was in the '80s that everyone wanted to write their own version of Flight Simulator. I used these algorithms to implement an Amiga-style windowing and graphics system on Atari STs.

It was in the '90s that things changed. I trace the moment to the release of the IBM RS/6000 processor. (Sorry, RISC System/6000. So many other companies had trademarks on "RS" terms that we were told not to use the acronym in anything official.) Its floating-point unit was so good that we would advise FORTRAN programmers to do calculations on integer values by converting from integer to floating point, doing all the multiplies and divides, and then converting back to integer.

The Promise of OO

Severin asked if I thought OO had fulfilled its promise.

Well, that's a question that almost by nature is impossible to answer.

Lots of colleges are teaching object-oriented programming right from the start, and lots of programmers adopted that style once they saw it becoming popular, so "widely adopted", sure.

For "fulfilled its promise", what exactly is the promise? If we think of people 100 years ago chopping down trees with axes and handsaws, and then the chainsaw came along, obviously a lot more people cut down a lot more trees today. But the professionals have in many cases cut down all the trees, which turned out not to be such a great idea. And the amateurs prune the trees in their back yards, but sometimes they cut off their own fingers as well.

When I run through this analogy in my mind, I can't help thinking that in the world of computing, our "chainsaw" -- whatever is the latest programming or implementation fad -- is often marketed both as a children's toy and as a personal grooming aid. :-)

OO style is a natural outgrowth when you're reaching the limits of other styles. For example, if you're doing functional programming and finding yourself writing slight variations of the same code over and over. Or, in the Oracle context, if you're writing procedural PL/SQL and find yourself wishing that you could plug in a variable at a spot where one isn't allowed, or you're writing the same code twice to deal with variables of different types.

But when I see people tackling straightforward problems requiring small amounts of code, sometimes I feel like it's overkill. I've seen plenty of code where someone contrived a hierarchy where one wasn't really needed, or where a lot of team effort went into making the hierarchy deeper, rather than coding the lowest-level classes that would actually do something useful.

Think of the common OO idiom of hiding all member variables behind getXYZ() and setXYZ() methods. If your project is going to employ tools that generate and compile source code dynamically, or a debugger that's going to hook in its own get and set methods ahead of the real ones, that technique makes perfect sense. It's enabling extra functionality, it's planning ahead to avoid problems in scalability and maintenance. But many programs are written to solve some limited problem, and the code is never going to be reused on such a scale, in which case the extra typing might not serve any purpose.

I like the way UC Berkeley does it in their introductory CS course (link goes to UCB course podcasts on iTunes). They run through various styles of programming, and only when they've demonstrated some of the limitations that OO is intended to solve do they introduce OO style. At that point, the assignments involve actually writing the guts of an OO system, so even if you're just running trivial OO code you're seeing how it all works under the covers.

Can Your Programming Language Do This... Or That...

Just wanted to point out this "Joel On Software" article, Can Your Programming Language Do This?. It's a nice concise opinion that summarizes why, given an arbitrary algorithmic problem, I'm personally more likely to turn to Javascript (or Perl or another scripting language with the same feel) than Java.

The key for me with Java is this statement from Joel:

Java required you to create a whole object with a single method called a functor if you wanted to treat a function like a first class object. Combine that with the fact that many OO languages want you to create a whole file for each class, and it gets really klunky fast.

I think of Java's OO nature as "hard" object orientation, in that choices are relatively set in stone based on how class files are organized and how the code flows. You can make variants of a class that do whatever you want, but you might have to do a lot of advance planning.

Conversely, in Perl, you can do like so:

sub something
{
...one set of code...
}

sub something
{
...another set of code...
}

You can call something(), and it picks up the last definition. Which makes it very easy to change the behavior of a program by overriding a method right before you call it, or by swapping the order of 'require' lines, or what have you. I think of this as "soft" object orientation, because it may be less amenable to mathematical proof of correctness, but it requires less planning and is less likely to produce cascades of syntax errors. Define it, call it, redefine it, call the new thing.

Most languages have some sort of dynamic execution facility. For example, with PL/SQL it's the EXECUTE IMMEDIATE statement and the OPEN-FOR statement. Any time you see a language concatenating strings and executing the result, you need to watch out for potential security problems. Who gets to enter that string, could it be rigged to execute multiple statements instead of one, could different names be substituted so it operated on some object you didn't expect. PL/SQL has the USING clause to guard against such problems by using bind values. Still, the Javascript approach of passing around functions as parameters feels comfortable, because all the variations of those functions are defined in your own source code rather than combined from arbitrary strings.

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.