Feuertip #12: De-nuke your SQL

SQL statements are generally considered to be atomic. For example, when I execute this update statement, either all employees in department 10 have their names upper-cased, or none do.

update employees
   set last_name = upper (last_name)
 where department_id = 10

Atomic -> nuclear, got it?

So what I want to talk about is how you can make your SQL statements non-atomic. Or, to put it another way, how to de-nuke your SQL.

There, job done. 🙂

Of course, generally, you do want your SQL statements to be atomic – all or nothing. But sometimes, well, you need to change things up a bit.

Time for a little bit of time traveling, all the way back to the end of 2008. It was a very good year for some, and then it got really, really bad for a whole lot of us. Because years of de-regulation of the finance industry in the United States led to a housing bubble the likes of which had not been seen for a long time. And then the bubble popped. Big time. On September 29, 2008, the Dow Jones Industrial Average dropped 777.68%. Panic ensued, because, in essence, capitalism as an economic system seized up and died.

The solution was obvious to the people who had caused the problem: get the government to give them enormous sums of money, no strings attached.

What they would do with all that money? Well, among other things, of course, they would give it to themselves.

“Bonuses paid to financial services workers in New York jumped 17% to $20.3 billion in 2009, the year that Wall Street firms received billions in federal aid.” (CNN)

Yes, that really happened. But now let’s take a behind the scenes look at what those poor folks in IT had to do in order to implement this get-richer-now scheme.

(Just in case there are any doubts: I am totally making up the following story.)

When you’ve got a whole lot of money to hand out to your employees, you can run into problems. The CEO at Too Big To Fail Bank decided that he wanted to give all his employees a bonus of 200x their salary. He called Steven, the IT person, to get it done.

What could be easier, with SQL?

update employees set bonus = bonus * 200

Easier to write, anyway. But when I ran it I got this error:

ORA-01438: value larger than specified precision allowed for this column

It turned out that some people could have their bonus increased by a factor of 200, but others already got such large bonuses that this multiplication makes the value too large to fit into the column. And because SQL is, by default, atomic, no one gets a bonus. Including me, the IT guy.

So I reported back to the CEO that I couldn’t do as he asked, and why. And he said: “We don’t have time to change the data model. So just give the bonus to anyone who can get it and leave the rest alone.”

(now you know I am making this up!)

As a PL/SQL IT person, I knew just what to do: go row-by-row!

begin
   for rec in (select employee_id from employees) loop
      begin
         update employees set bonus = bonus * 200
          where employee_id = rec.employee_id;
      exception
         when others then
             null;
      end;
   end loop;
end;

Sure, each update is still “atomic”, but all or nothing now means “update 1 row or raise an error”. If one row cannot be updated, then I handled the error (“Who cares? Keep going!”) and tried to give the next person their ginormous bonus.

Problem solved, right?

Well, maybe, sort of. Everyone who could get the bonus, would. But being an excellent IT person, I actually tested the code in our stage environment. And, lo and behold, against real employee data, it took too long. Because “row-by-row is slow-by-slow” (thanks for that, Tom! ).

At that point, I could have used bulk collect and forall to speed things up, but I would still be updating one row at a time. It’d be better if I could just use a single update statement – but didn’t I just prove that I couldn’t?

Yes and no. I could not get the single, all -in-one update statement to update all rows, because I insisted upon the atomicity of that statement.

Wouldn’t it be nice if I could tell Oracle Database to do something like the following?

“Let me update all the rows that can be updated. If you encounter a problem with any particular row, save the information for me to look at later, but keep on going. In other words, drop the atomic.”

Yes, yes it would be nice. And you can do precisely that with the log errors feature of SQL.

You can see and learn lots of details about log errors in this LiveSQL script.

I also covered this topic in some detail here.

Oracle-Base (Tim Hall) does his usual outstanding job presenting log errors here.

So I’ll just give you the highlights in the rest of this post. Also,  I am going to switch to updating salary, not bonus, since the standard employees table doesn’t actually have a bonus column.

Here’s an update statement that includes the log errors clause. I specify (declaratively, of course, since this is SQL) that any errors should logged into a table named err$_employees, along with a “tag” consisting of the first 20 characters of the last name. Oh, and I don’t care how many errors are encountered. Just keep on going!

update employees
   set salary = salary * 200
   log errors into err$_employees (substr (last_name, 1, 20))
   reject limit unlimited

And it does “keep on going”. This update statement will not fail if an error is encountered along the way. It will update as many rows as it can. No need for cursor for loop with nested block. No need for bulk processing.

But lots of need for care with using this feature! Before I get to that, however, a few pointers on how to get this to work. First, you must create that error logging table, best done through the dbms_errlog package:

begin
   dbms_errlog.create_error_log(dml_table_name => 'EMPLOYEES');
end;

You can also create this table yourself, manually, with a create table statement, as long as the table has the following five columns:

Name            Type
--------------- --------
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$   VARCHAR2
ORA_ERR_ROWID$  UROWID
ORA_ERR_OPTYP$  VARCHAR2
ORA_ERR_TAG$    VARCHAR2

In other words, there’s no “magic” here. You just need a good, old relational table.

Then if you include the log errors clause in your SQL statement, when the SQL engine encounters an error, it:

• looks for a table of the specified name (err$_ is the default, but you can change that if you’d like)
• if found, inserts a row with those standard columns set to the error information and tag
• also populates additional columns with names matching those in the dml table with the values at the time of the error
• commits that insert (from within an autonomous procedure, so nothing else is committed) and pretends nothing went wrong and continues.

A very nice “under the covers” feature – but one that you should use very carefully for these reasons:

• Very few situations require or find desirable making a SQL statement non-atomic. Do you really want to break that very important characteristic?
• The error logging table leaves a bit to be desired. It does not record when the error occurred. It does not record who caused it. It does not record where it came from in your code.
• But the good news is that you can add your own columns – and triggers! – to that error logging table and make it “whole.”
• You must must must check the error log table immediately after the SQL statement executes, otherwise, you will have no way of knowing what if anything went wrong.
• You need to decide what to do with those errors. Often you will “move” the error information into your standard error log for your entire application.
• Just because you have privileges on the dml table, you will not necessarily have privileges to insert into the error logging table.

I’ve built a “helper” package on top of dbms_errlog to make it easy to fix some of the above limitations. You’ll find it here on LiveSQL.

The log errors feature is very cool and a reflection of how long Oracle Database has been around. It’s given those smart developers at Oracle HQ the time and opportunity to finesse ways for us to tweak default behavior safely through APIs and declarative interfaces.