Skip to content

PL/SQL

I recently wrote a basic data transformation program using Java and PL/SQL. I hadn’t used PL/SQL (which is an Oracle-specific procedural language for stored procedures) since writing a basic data layer for my first professional project (a Yahoo! like application written in PL/SQL, perl and Story Server–don’t ask). Anyway, revisiting PL/SQL reminded me of some of the things I liked and disliked about that language.

I like:

Invalidation of dependencies. In PL/SQL, if package A (packages are simply arbitrary, hopefully logical, groups of procedures and functions) calls package B, A depends on B. If the signatures of B are recompiled (you can separate the signatures from the implementations) package A simply won’t run until you recompile it. This is something I really wish other languages would pick up, because it at least lets you know when something you depend on has changed out from under you.

I dislike:

The BEGIN and END blocks, which indicate boundaries for loops and if statements, are semantically no different than the { and } which I’ve grown to love in perl and Java. But for some reason, it takes me back to my pascal days and leaves a bit of a bad taste in my mouth.

I’m unsure of:

The idea of putting business logic in a database. Of course, schemas are intimately tied to the business layer (ask anyone trying to move to a different one) and anyone who pretends that switching databases in a java applications is a simple matter of changing a configuration file is smoking crack, but the putting chunks of business logic in the data layer introduces a few problems. Every different language that you use increases the complexity of a project–and to debug problems with the interface between them, you need to have someone who knows both. Also, stored procedures don’t fit very well into any of the object relational mapping tools and pretty much force you to use jdbc.

One thought on “PL/SQL

  1. Tom Malaher says:

    Yeah, and it’s kinda like using JNI (native interface to C code) in that you’re jumping out of the JVM and who knows what the code is going to do.

    You can put whatever object oriented wrapper you want around the call to the stored proc, but there’s no hiding the fact that:

    a) the code is elsewhere, and you have to go there to find out what it does, and it’s in a different language, and under some other revision code control system (it is under revision control, isn’t it?)

    b) it may be doing stuff that you couldn’t/wouldn’t do in Java and/or having side effects, and/or bypassing safeguards that would be there if the same logic was executed by Java. (one thing that comes to mind there is invalidating caches… you have no idea what tables the PL/SQL might be fiddling, so you really should assume that *anything* might have changed, and invalidate all your caches. Whereas if Java were doing the logic, you could explicitly invalidate only those caches you know you affected.)

Comments are closed.