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

FUNCTION getempid (employee_id IN plch_employees.employee_id%TYPE)
   RETURN plch_employees.employee_id%TYPE
IS
   the_id   plch_employees.employee_id%TYPE;
BEGIN
   SELECT plch_employees.employee_id
     INTO getempid.the_id
     FROM plch_employees
    WHERE plch_employees.employee_id = getempid.employee_id;
   RETURN getempid.the_id;
END getempid;

(notice that the INTO and RETURN parts don’t actually need to reference the function name to refer to the local variable, but I’ve done it this way for clarity)

In the case of an anonymous block, it is necessary to supply a label for the block:

<<getempid>>
DECLARE
   employee_id   plch_employees.employee_id%TYPE := 100;
BEGIN
   SELECT plch_employees.employee_id
     INTO getempid.employee_id
     FROM plch_employees
    WHERE plch_employees.employee_id = getempid.employee_id;

   DBMS_OUTPUT.put_line (getempid.employee_id);
END getempid;

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 🙂

Jeffrey Kemp 30 December 2010

the-feuerstein-challenge /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Multi record select form and record locking
A simple data ETL method – nothin’ but SQL

Comments

  1. poelger
    30 December 2010 - 7:37 pm

    Good quiz Jeff!
    Altough I would avoid those naming conflicts by using a scope prefix for my variables, e.g. l_employee_id for a local variable.

    But like I said, good quiz!

    • Jeffrey Kemp
      30 December 2010 - 9:06 pm

      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:

      FUNCTION get_abc (p_def IN INTEGER) IS
      v_abc INTEGER;
      BEGIN
      SELECT abc INTO v_abc FROM xyz WHERE ghi = p_def;
      RETURN v_abc;
      END;

      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.

  2. poelger
    30 December 2010 - 10:47 pm

    Interesting point, never got into that problem.

  4. gary
    14 January 2011 - 8:04 am

    Just sent off my first quiz.

    I checked the Interactive Report and saw you didn’t enter on your day.

    Do you automatically get excluded, or are you told when it comes up so that you don’t take it that day ?

    • Jeffrey Kemp
      14 January 2011 - 8:12 am

      Good on you Gary, I look forward to it 🙂

      I didn’t enter the quiz on my day – Steven emailed me the day before to let me know.

      I don’t know if the site would have excluded me or not, but it would have been a hollow victory if I’d got the quiz right – and would have been rather embarrassing if I got it wrong 🙂

      • Jeffrey Kemp
        3 March 2011 - 1:57 pm

        Just a follow up in case someone stops by – Steven has officially allowed quiz authors to answer their own quizzes – not only that, but they get 0 seconds as their time to answer 🙂

Leave a Reply

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