My First Quiz

I was pleased to see my PL/SQL quiz question presented yesterday. It was about how PL/SQL variable names and other identifiers whose names conflict with names of tables and columns can still be referenced in SQL within PL/SQL.

It demonstrates how a local variable or parameter of a procedure or function may be referred to unambiguously, by referring to it by the name of the procedure or function, e.g.:

The quiz answers also explore what happens if a variable name conflicts with a column name, and the identifiers are not referenced; another answer considers the case where the block label happens to conflict with a table name.

I heartily recommend thinking about and writing your own quiz questions and submitting them for Steven to consider for the Challenge. If you do, here are some tips:

Focus on one topic – remove any irrelevant details

Build a full test case and run it (and re-run it carefully after every single change you make!)

Try to remove as much code as possible (without ruining it)

Re-read the question the next day, and imagine being a typical Challenger, who nitpicks every single statement :)

Thanks, Gert. You’re right – most places where I’ve worked have used naming standards to automatically differentiate between variables, parameters and column names. Unfortunately, not always very consistently.

While I don’t have a problem using those same standards, in fact would generally prefer them, I think there is a good argument to always use explicit referencing instead.

I once came across a problem with a piece of code that looked something like this:

The problem was, this function always raised the NO_DATA_FOUND exception, even when the incoming parameter should have matched a row in the table. Can you see the reason why? It took a while to discover the problem: the table xyz had a column “p_def”. And on reflection, the column name was quite logical in the context of that table – it was an accident that it happened to also look like a parameter name, because of the naming standard being used.

I won’t say this is a reason not to use naming standards, but over the years I’ve started preferring the method of always providing explicit references to PL/SQL variables when used in embedded SQL, in order to remove all ambiguity – sure, it makes the code a bit wordier, but it does make it clearer once you get used to it. In addition, it always works – unlike prefixes, which the compiler will not check for you.