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