Qualified identifiers and error messages - 29 December quiz (1823)

The 29 December quiz tested your knowledge of how you can qualify the names of PL/SQL elements with their scope name (procedure, function, block).

Iudith wrote the following commentary regarding the kinds of errors that are raised across different versions of Oracle:

Regarding the Quiz of 29-dec, Choice 2:

<<plch_employees>>
DECLARE
employee_id plch_employees.employee_id%TYPE;
BEGIN
SELECT plch_employees.employee_id
INTO plch_employees.employee_id
FROM plch_employees
WHERE plch_employees.employee_id = plch_employees.employee_id;
DBMS_OUTPUT.PUT_LINE(plch_employees.employee_id);
END plch_employees;
/

The choice is anyway incorrect, but maybe it is worth to remark that the PL/SQL compiler treats it differently in the different database versions:

1. For Oracle 10.2.0.3.0, the full compiler errors are as follows:

ERROR at line 3:
ORA-06550: line 3, column 17:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 3, column 17:
PL/SQL: Item ignored
ORA-06550: line 6, column 12:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 7, column 7:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 5, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 10, column 42:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 10, column 5:
PL/SQL: Statement ignored

2. For Oracle 11.1.0.7.0, the compiler errors are somewhat different:

INTO plch_employees.employee_id
*
ERROR at line 5:
ORA-06550: line 5, column 12:
PLS-00403: expression 'PLCH_EMPLOYEES.EMPLOYEE_ID' cannot be used as an 
INTO-target of a SELECT/FETCH statement
ORA-06550: line 6, column 7:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 42:
PLS-00357: Table,View Or Sequence reference 'PLCH_EMPLOYEES.EMPLOYEE_ID' not allowed in this context
ORA-06550: line 9, column 5:
PL/SQL: Statement ignored

That is, for Oracle11gR1 the PLS-00403 and PLS-00357 errors appear, while in Oracle10gR2 we saw the PLS-00320 error.

So, things change over time, and, in this case, 11g looks more explicit.

Also, the "hiding" of the %TYPE anchoring caused by using a table name as a label can be worked around not only by defining the variable as INTEGER, but also by qualifying the table name with the schema owner in the variable definition, and thus a %TYPE anchoring can still be used.