The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.

03 May 2011

When to use the NULL statement? Many objections raised! (2281)

The 2 May quiz question and multiple choices were brief and free of any code....so of course one would expect that the quiz would generate many responses from players. The question was: "Which of the following statements describe a reason to use the NULL statement in your program?" And the choices given were:

To allow a GOTO statement to transfer control to a label at the end of your subprogram (at which point there's nothing left to do).

To improve readability by making the meaning and action of conditional statements clear.

To create "placeholders" in your code, also known as "stubs", so that your code can compile even if you are not yet done.

To document that you have reached the end of your block and there is nothing more to do.

The first three were scored as correct, the last one as incorrect. Rather than offer some of the various comments here, I will leave it to players to post their opinions (please do read the answer text before you do so). I will simply say this:

2. I did not ask you to decide which choice was a good reason or a best practice regarding the NULL statement, simply a reason to use it.

3. You might not like GOTOs, you might think that a GOTO is a really bad idea and should never be used. But the fact is that it is a part of the PL/SQL language - and the PL/SQL development team doesn't implement constructs without a reason or legitimate use. You can say the same for the NULL statement.

So I do not plan to change the scoring on this quiz, but please do post your comments and we will go from there.

Finally, for those of you who vow to never to use a GOTO and claim to believe that there is never any reason to do so, please check out this analysis from Steve McConnell and Code Complete.

18 comments:

I do not agree with the last given answer to be incorrect. As I understood the Answer ID:6148: "To document that you have reached the end of your block and there is nothing more to do." I'd imagined this would talk about a block like this:

BEGIN NULL; END;

In this case it's necessary that you include a NULL! Otherwise your code will not compile. Actually I considered it to be just a variation of the answer ID 2543 before, but explicitly for a block this time. It could also apply to a: BEGIN /* long comment.... */ NULL; END; where you explicitly explain in your comment why there is no code in the block here. Might also be a placeholder for future code to be included that is currently out-commented. The whole block does add to the code base, but that has to be weighted against the benefit for a programmer to easier understand what's going on here. As explained in your answer, sure the extra NULL; after lots of other statements in a block doesn't make sense and the argument is valid in this case. The statement given however is too vague to just allow for this interpretation in my opinion and therefore the answer shouldn't be counted.

Ouch. I had written twice, once when filling in, because I expected too lots of complaints and once when I read the answers. And I said "ouch" because it is in the Oracle documentation.

Ah well, discussions about what programmers like and do are endless and have no real use. And if this "answer" is coming from Oracle, who am I to correct the opinion from Steve. It was not his opinion.

You can point out an opinion about something and then leave it be.

My opinion? - I don't like GOTO's but sometimes they come in handy. - NULL as a placeholder for GOTO? Bad idea. - NULL with comment for not finished code. Good idea.- Readibility in action of conditional statements. Good idea, but again comment to prove that you had given that option thought.

Discussion about reasoning? If there is no good reson or it is not best practice, then there is no reason to use it, but that is not an important discussion.

I'm go reading the analysis, although I believe that sometimes using GOTO could be useful.

I disagree, that using NULL in conditional statements improves readability. In my opinion, when you are used to use NULL as a placeholder for unfinished code to make it compilable, it may decrease the readability, because such NULL may be mistaken for the placeholder (and someone puts some code in ELSE part). Besides this technique produces unnecessary lines of code and this also decreases the readability (I've read to many spaghetti code in my practice). But maybe this is just my point of view.

1. The correct choices were taken directly from the Oracle documentation.We have seen before, that this is not a valid argument ;-)

@kubicki: I use NULL for unfinished code every time when I implement more than a small procedure, but it is written NULL; --TODO so it is easy to distinguish.

@Steven: I read the linked documents and nevertheless I'm not convinced and will mark the choice as incorrect as a personal opinion, should I ever be asked again (maybe not in a OCM-Exam).

Also this situation usually will not arise in my coding, because of the instrumentation that will log the last line of a procedure in verbose mode. So I will always be able to label this call for the GOTO.

RegardsMarcus

P.S.: The first thought when I read this choice was: How long will it take for a blogpost about this?

This feels less like a quiz on the PL/SQL language and functionality and more like an opinion poll on how programmers work. It wouldn't hurt my feelings if the quiz were thrown out. But since it sounds like that won't happen, I'd like to request that there not be any more quizes of this nature. I don't want to be quized on the documentation and Oracle's opinions!

Disregarding arguments for, or against, the use of GOTOs, the use of a NULL statement following a label is a workaround for an Oracle imposed PL/SQL limitation (Oracle could have chosen to permit labels at the end of blocks without requiring the label to have an associated executable statement). If one chooses to use GOTO to skip past the remaining code in a block, this is a reasonable mechanism. There may be cases where the use of GOTOs results in more readable and maintainable code, but I have never encountered one.

I agree with using NULL statements (when preceded by an appropriate "TODO" comments) as placeholders for code that has yet to be written. I generally develop code iteratively and perform unit testing as I proceed. The use of "TODO" comments and the "unreachable code" warnings when compiling serve to remind me that some portions of the code are not yet complete.

I adamantly disagree with the use of NULL statements to clarify the meaning of conditional statements. Adding additional code that does nothing does not improve readability. Within CASE statements I may use a NULL statement in the ELSE clause in order to avoid a runtime exception when none of the WHEN conditions have been met. Using a NULL statement in the ELSE clause of an IF statement to indicate that nothing should be done when the condition has not been met is redundant (by definition the statements within an IF are only performed if the condition is met) and is indistinguishable from the use of NULLs as placeholders unless appropriate comments are included. If one feels that such commentary is necessary to clarify or explain the absence of actions when a condition is not met, then such commentary can precede the IF statement. Adding a NULL statement in this case provides no additional value.

I took a long time deciding whether or not to select the choice regarding improving readability; ultimately deciding that it is was likely that some misguided individual considered this a good practice. Although I’m glad I received credit for a correct response, I feel a little dirty for it. Had the question been prefaced with "according to Oracle’s documentation," then I could completely agree with the correct answers; however, in the absence of such phrasing, the responses seem to be more a matter of opinion than fact. Can one’s opinion be wrong?

I occasionally use a NULL in the first option of an IF, such asIF v_opt_out = 'Y' THEN NULL;ELSE =code=END IF;

I use it where it is easier/clearer to express the condition where you DON'T want the conditional code invoked. Normally that's because NULLs are involved in the condition so that you can't simply use a NOT.

I don't use GOTO but have been guilty of using user-defined exceptions as a pseudo-GOTO to jump to the end of a procedure/function. I think I've broken that habit though.

In fact my biggest use of NULL is in exception handlers such asBEGIN SELECT spouse_id INTO v_spouse_id FROM ...; process_spouse(v_spouse_id);EXCEPTION WHEN NO_DATA_FOUND THEN NULL;END;

In all my nearly 30 years of programming I have never heard (and never seen) anything about using a null statement to improve the readability of conditional statements.

There are only two reasons to use a null in a branch of a conditional statement:

1. to use it as a stub for something that you want to implement later on (but that is answer-id 2543)

2. the usage in the ELSE-branch of a CASE-statement, if you really want to ignore all not catched cases (otherwise you would receive an exception if such a case occurres).

In my opinion, if you need to make it clear, that there is no need for an empty branch, you should write it into a comment for the THEN-branch, that makes clear, why there is no branch, but don't create additional code.

And if I would read such code, I would assume that the deveoper hasn't fully coded the logic he wanted to implement and has forgotton to code that branch.

This was basically my feedback on the quiz, also expecting it from others:"in regard to using it as a way to document the end of a block, I think this will be interpreted very subjectively. While I never do it, nor have ever considered it, I can see how some people may choose to signify the expected end of a block/section of logic. Looking back to check the wording, I'm not sure why I left that option ticked - perhaps because I thought it may be considered by some as a 'reason' to use it, while I disagree"

I think using it to signify the end of a block is poor coding, I also think using null to show that no action is necessary is poor coding - that's what comments and instrumentation are for.

But since the question asked for a 'reason', not necessarily a good one, I deliberated for quite a while wondering what the expected answer would be. Oh well!

As I have already written in a previous comment (that for some reasons did not appeared here), the word "reason" implies the "reasonable usage", not the "possible usage". E.g. is there a reason to write so v number := 1-1;?I guess, no. Is it possible to write so? Definitely, yes. The same with the NULL statement for a "clarification" of conditional statements.

"To allow a GOTO statement to transfer control to a label at the end of your subprogram (at which point there's nothing left to do)."

This is what RETURN is for: Nothing more to do, get out of the block. "a reason to use" in the question implies that you would gain something from doing it, and you don't in this case. However, I gather that many coders believe that only having one way to exit a block improves readability. I do not subscribe to this view.

Still, I ticked the box. My reason for this is that if you did have a wish in the first place to skip to the end of the block, the NULL statement would be necessary to accomplish it. So I got a correct choice on the quiz, but feel dirty about it.

Consider conditional compilation. The following block would not work without the NULL statement:

BEGIN LOOP ... IF ... THEN GOTO end_of_program; END IF; ... END LOOP;

<> $IF TEST_SYSTEM $THEN log something $ELSE NULL; $ENDEND;

And remember, the question was not about good reasons for using GOTO.

However, wouldn't "RETURN;" as a last statement in a procedure do the same as "NULL;"? But the term "subprogram" might possibly as well mean a block within a procedure or function. Then we could not replace "NULL;" with "RETURN;".

Gary, I would suggest that the IF construct you described would be more clearly written as:

IF v_opt_out IS NULL OR v_opt_out <> 'Y' THEN-- CODEEND IF;

This construct explicitly states the conditions under which the code will be executed and does so without the use of explanatory commentary (and without a NULL statement that could be misunderstood to be a stub awaiting completion). Because comments are not executable code, they can be misleading or even wrong without affecting application behavior. Ideally code should be written such that few comments are needed. Clean Code: A Handbook of Agile Software Craftsmanship, by Robert C. Martin, has an excellent discussion of this. Although the examples in the book are Java centric, the principles transcend any specific programming language. (By the principles in the book, the details about how selection is indicated should be moved to a function to improve readability and maintainability. Isolating the implementation details in this way reduces the number of code points that may need to be modified if the conventions for indicating selection are changed.)

In general, if no action is to be performed, then no code should be written. Ignoring unimportant exceptions, which you mentioned, is one case in which this rule may have to be ignored (although one could argue that in this case the NULL statement is effectively performing the action of discarding the exception).