Feuertip #5: Just say no to developer insurance

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

   dbms_output.put_line (l_name);
end;

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:

declare
   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.