By: Steven Feuerstein On: April 14, 2021 In: APEX Developer Solutions, Feuertips Comments: 0

You’ve never heard of developer insurance?

That’s probably because I made it up. 🙂

A developer takes out “insurance” when they write code to “make sure” or “just in case”. Generally, we do this because we are not confident enough in our understanding of the programming language.

Here’s a simple and common enough example:

   l_name varchar2(100) := null;
   if to_char (sysdate, 'D') = 1
      l_name := 'Hard Worker';
   end if;

   dbms_output.put_line (l_name);

I assign a default value of null to l_name. Why would I do that? Here you go:

“Hmmm. I want to make sure that variable is null if today is not Sunday. So I am going to initialize it explicitly to that value.”

What’s the problem? This is entirely unnecessary.

Anytime and always, when you declare a variable, it is automatically assigned a value of NULL if you do not provide a default value.

One could argue: “Well, maybe Oracle will change that behavior in the future, and I really need to be sure it is always set to null initially.”

And that’s not a bad attitude to have, but not if there really is NO WAY (see that? bold + italic + caps. I really mean it) that such a change will ever happen. I mean, c’mon, imagine the impact on hundreds of thousands of applications if that changed.

It would be instantly identified as a major regression and be patched back to null-dom ASAP.

If you are not 100% clear on how a certain feature in PL/SQL or any other language works, don’t add extra code “just to be sure.” Instead, go to the language documentation (the source) and be sure – about how that feature works!

Here are some other examples of common developer insurance in PL/SQL:

“I delete all the elements from a collection before filling it with a bulk collect query.”

No! Bulk collect always wipes out that collection for you.

“I’ll declare a variable for the for loop iterator to make sure it compiles.”

No! No! No! PL/SQL always implicitly declares an iterator (integer or record) for your for loops. If you declare one yourself, it will not be used inside that loop.

“I use trunc with date literals to make sure there is no time component.”

Silly! The date literal never includes a time component. Oh, you don’t know what the date literal is? Here you go:

   l_a_very_bad_day date := date '2021-01-06';

“Whenever I write a select-into, I always include a no_data_found handler.”

OK, well, fine. This is not always an example of developer insurance, but sometimes it is, namely when you are executing a group function like sum or count. In this case, no_data_found is never raised.

I bet you can think of other examples of developer insurance. Please add them below in the comments, so we can all learn about more code patterns…to avoid.


Every other Wednesday at 11 am ET, I go live on YouTube and Facebook with Michelle Skamene to provide you with a not-to-be-missed PL/SQL tip and fun conversation. Sure, we record it so you can always watch later, but live is so much more rewarding!
One participant will be selected at random to choose from three organizations dedicated to the preservation of our planet and its biodiversity.
Insum will then make a donation of $25 to that group in your name.
What could be better than levelling up your PL/SQL tips and helping one of these worthy organizations? Join us every other Wednesday at 11!

Photo Credit: Antoine Petitteville Unsplash[/vc_column_text][/vc_column][/vc_row]
Share this:

Leave reply:

Your email address will not be published. Required fields are marked *