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 here
BEGIN
  -- code goes here
EXCEPTIONS
  -- exception handling code goes here, using WHEN clauses
END;

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 actions
END;

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 ROW
BEGIN
  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 ROW
BEGIN
  DECLARE
    pl_error_id table1.error_id%TYPE;
    CURSOR get_seq IS
	SELECT table1_seq.nextval
	FROM   dual;
  BEGIN
    OPEN get_seq;
    FETCH get_seq
	INTO pl_error_id;
    IF get_seq%NOTFOUND
    THEN
	raise_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.