Skip navigation.

gojko's blog

Catalogue of SQL & PL/SQL Bad Practices

gojko's picture

I have started compiling a list of Oracle PL/SQL and SQL bad practices, with the intention of producing a comprehensive catalogue of common programming errors, that can be used as a check-list for code reviews or given to junior developers so that they can learn from the mistakes of others.

For each bad practice, I provided a list of symptoms in the code, an explanation why it causes problems and a list of preferred solutions. I have also listed exceptions to the rules, when they exist.

What a strange way to write NULL

gojko's picture

A few days ago, while hunting for a bug in PL/SQL code, I stumbled upon the strangest way to write NULL. If the e-mail address parameter was empty, the genius who wrote this PL/SQL procedure set it to , then compared it with ten lines below, in order to log a problem. I really don't know what is it about NULL that scares people so much, but over the years I got used to occasional -1 and 0, or even 'EMPTY'. However, this is the first time I ran across Donald.

A better view

gojko's picture

Accessing the database from the outside world basically comes down to two options - direct querying or executing stored procedures. Procedural access is often chosen for the wrong reasons - making maintenance significantly harder.

Best practice to corrupt data

gojko's picture

A common practice for handling errors in PL/SQL procedures is to catch all errors in the top-most database layer and convert them into error codes and human readable messages for client applications. This technique is a relict from the past and, in fact, a very bad practice from today's perspective, since it can lead to data corruption.