CodeSOD: This Is Your Brain on PL/SQL

The realest of real WTFs, the reigning champion for all eternity is and forever will be Oracle. Today, we're going to take a look at a little code in PL/SQL.

PL/SQL is a weird language, a blend of SQL and, well, a Procedural Language, with a side of OO slapped on. The syntax does an excellent job giving you the feeling that it was designed in the 1970s, and each new feature or change to the language continues that tradition.

The structure of any PL/SQL code unit is going to be built around blocks. Each block represents a self-contained namespace. The basic anatomy is:

DECLARE -- variable declarations go hereBEGIN -- code goes hereEXCEPTIONS -- exception handling code goes here, using WHEN clausesEND;

If you're writing a stored procedure, or a trigger, you replace the DECLARE keyword with CREATE [OR REPLACE]. You can also nest blocks inside of other blocks, so it's not uncommon to see code structured like this:

BEGIN DECLARE --stuff BEGIN --actions END; --more actionsEND;

Yes, that does get confusing very quickly. And yes, if you want to really approximate structured error handling, you have to start nesting blocks inside of each other.

The language and database have other fun quirks. They didn't get an IDENTITY column type until version 12c. In prior versions, you needed to use a SEQUENCE object and write procedures or triggers to actually force the autonumbering. You'd usually use a SELECT INTO" statement to populate a variable, with the bonus that Oracle SQL always requires a table in the FROM clause, so you have to use the made up table dual, e.g.:

CREATE TRIGGER "SOME_TABLE_AUTONUMBER"BEFORE INSERT ON "SOME_TABLE"FOR EACH ROWBEGIN SELECT myseq.nextval INTO :new.id FROM dual;END;

:new in this context represents the row we're autonumbering. That's the "normal" way to create autonumbered columns in older versions of Oracle. Boneist found a different, slightly less normal way to do the same thing:

CREATE OR REPLACE TRIGGER "SCHEMA1"."TABLE1_TRIGGER" BEFORE INSERT ON "SCHEMA1"."TABLE1" FOR EACH ROWBEGIN DECLARE pl_error_id table1.error_id%TYPE; CURSOR get_seq ISSELECT table1_seq.nextvalFROM dual; BEGIN OPEN get_seq; FETCH get_seqINTO pl_error_id; IF get_seq%NOTFOUND THENraise_application_error(-20001, 'Sequence TABLE1_SEQ does not exist');CLOSE get_seq; END IF; CLOSE get_seq; :new.error_id := pl_error_id; END;END table1_trigger;

There's a lot going on here. First off, note that our DECLARE section contains a CURSOR statement. Cursors let you iterate across records. They're very expensive, and in Oracle-land, they're a resource that must be released.

This trigger uses a nested block for no particular reason. It also uses an extra variable, pl_error_id which isn't necessary.

But the real weird part here is the IF get_seq%NOTFOUND block. That's pretty simple: if our cursor didn't return a row. This is something that, with this cursor, can't possibly happen, so we'll never hit this. The sequence will always return a value. That's a good thing, if you look at the code which follows.

raise_application_error is Oracle's "throw" equivalent. It will crawl up the stack of executing blocks until it finds an EXCEPTIONS section to handle the error. Note that we close the cursor after that statement- thus, we never actually close the cursor. Cursors, as mentioned, are expensive, and Oracle only lets you have so many of them.

Here we have a weird case of a developer defending against an error that can't happen in a way which would eventually lead to more errors.

[Advertisement] Otter - Provision your servers automatically without ever needing to log-in to a command prompt. Get started today!