I thought I’d start a loose series on PL/SQL for server-side developers. As a developer who has had to defend my use of PL/SQL in various systems over the years, I have some pretty strong feelings about what it brings to the table. I think of PL/SQL as a first-class language. That’s not to say that it can be used wherever Java or PHP are. What I love most about PL/SQL is that it fills some major gaps that Java and PHP (and most other traditional programming languages) have. When it comes to manipulating the database, anything Java can do PL/SQL can do better. In the context of the modern database application, that means that PL/SQL is an essential piece of any system.

Server-side developers in general have some serious hangups about PL/SQL. For one, it looks weird. What? No braces?!?!? Impossible!

Look, it’s not Java. Heck, it’s not even PHP. PL/SQL is its own beast, and you have to learn how to pet that beast so it doesn’t turn on you (and take your database down with it). If you think of PL/SQL as a simple means to tie some logic around DML operations (select/create/update/delete) it begins to make a lot more sense. It’s not supposed to be elegant. It’s not supposed to require hours and hours poring over thick books with fancy titles. It’s supposed to help you build better database applications, and at this I believe it excels.

So what’s so great about PL/SQL? Here are my canned responses to that question whenever some upstart developer starts spewing the crap he read out of his textbooks:

1. PL/SQL is compiled in the database. It always amuses me that a community like Java, which lives and dies by strong compile-time typing, is perfectly willing to let a major component of their application be loosely typed. You know all those JDBC calls/Hibernate mappings/iBatis queries? Little news for you Java dude. They’re completely unchecked. Put in terms you might understand – when you enter in a period, there’s no code assist to help you figure out how to complete the query. If I go in and modify the database in a few discrete ways your app will crash and burn. And you probably won’t realize this until a user sends a nasty email about why they can’t access the product they purchased. Not the case with PL/SQL. Since Oracle keeps them compiled in the database, you (or more likely the DBA) will know immediately if something changes in such a way that breaks the procedure or package.
2. Since they’re compiled in the database they will run orders or magnitude faster than the corresponding queries requested by a client application. The important concept here is called context switching. In short, it turns out all those trips back and forth the database tends to slow things down. It’s much much quicker to bundle up related queries in a procedure and make one call to the procedure. I once had an argument with a Java developer about result set sorting. He was convinced that it was much faster to sort a list of objects in Java than it would be to have the database do the ORDER BY and return the results. I like the guy, but that’s just insane. The overhead of fetching each of those rows and then doing some lame bubble sort on them is astronomical. But this is the kind of thinking that infests the server side community. It’s borne out of ignorance, sometimes willful, of what a database can do.
3. Another benefit of being in the database – they can be used by any client, not just ones written in Java (or PHP, etc.). When they talk about code reuse Java developers apparently don’t consider these kinds of issues. I’m sure it’s a wonderful learning experience to write a shipping cost calculator in Java, PHP, and JavaScript, but wouldn’t it make more sense to write it in PL/SQL once and then use it everywhere? Just a thought.
4. Believe it or not, most of the good DBAs I’ve worked with prefer complex logic to be wrapped up somewhere they can keep an eye on it. Remember, if something breaks at 3am they’re the ones that will get paged. Having all that business logic tucked away in a jar file somewhere makes then nervous. And when things do go bad they can help a lot more when the code is in the database. It’s better for everyone.
5. It takes about 15 minutes to learn enough PL/SQL to export some logic to the database. Sure, PL/SQL goes deeper than that, but any curly-brace type programmer should be able to absorb the concepts easily.

Now, I’m not going to say it’s all win-win. Moving business logic into the database has a dramatic effect on system design. You’ll find a lot less justification for something like Hibernate, for instance (ok, maybe that’s a win). I’ve been through this a couple of times, I know it’s hard to find the appropriate place to draw the line in terms of what gets moved into the database. Should you go balls to the wall and have the database return cursors for select statements? I usually don’t, but I have in some instances. Should every insert/update/delete be wrapped in a stored proc? Again, not an easy call.

In my most recent fantasy baseball app, I let the client code only insert into temporary tables, and then called a stored proc to validate the data and move it into the destination table. People look at me like I’m crazy when I tell them about this. But you know what? I’d do it again. If you buy the premise that inserts are dangerous because Java code can’t type-check them, then it’s the right way to do it. Temporary tables and stored procedures are much easier to change than Java code at most “serious” companies. It’s a matter of necessity to do it that way.

Hopefully I’ve covered the “whys” of PL/SQL convincingly enough. In a future post I’ll cover some basics of the “hows”.

Be Sociable, Share!

•