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.

"Bounds" for associative arrays - correcting an ambiguity - 28 December (239)

The 28 December quiz on associative arrays scored the following as incorrect:

There are no upper or lower bounds on the integer values you can use as index values.

Several players wrote to complain about this scoring, from two angles:

1. "If the array is indexed by binary_integer then there is upper and lower bounds. (-2147483647 .. +2147483647) However if the table is indexed by varchar2, then there are no bounds on the 'integer values'"

2. "There are no upper or lower bounds on the integer values you can use as index values.Actually, there is a limit on the index values, but it is defined by the limit on the BINARY_INTEGER. So I think there is NO actual limit on the index values, just the limit on the BINARY_INTEGER value."

3. One player quoted from my book, Oracle PL/SQL Programming, as follows: ""Unbounded versus bounded A collection is said to be bounded if there are predetermined limits to the possible values for row numbers in that collection. It is unbounded if there are no upper or lower limits on those row numbers. VARRAYs or variable-sized arrays are always bounded; when you define them, you specify the maximum number of rows allowed in that collection (the first row number is always 1). Nested tables and associative arrays are only theoretically bounded. We describe them as unbounded, because from a theoretical standpoint there is no limit to the number of rows you can define in them."

I agree with point 1 (that is, I accept that I was not explicit enough in my phrasing) and disagree with points 2 and 3. My explanations follow:

1. The argument here is that if the associative array is indexed by VARCHAR2, then you can run code like this without any error (provided by one of the players):

SQL>declare
  2     type ty is table of number index by varchar2(100);
  3     tb ty;
  4  begin
  5     tb(2147483648) := 1;
  6  end;
  7  /

while this code fails:

SQL>declare
  2    type ty is table of number index by binary_integer;
  3    tb ty;
  4  begin
  5    tb(2147483648) := 1;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 5

Now, I could argue that if you index by VARCHAR2, then the values used as index values are not integers; they are strings. So I think I could stand firm and insist that this statement is correct, but the bottom line is that from the perspective of a developer taking advantage of this "workaround" she is using "integer values" as the index values.

So I am going to change the wording of this choice to be more explicit, give everyone who select incorrect credit, and rescore.

2. I find this argument to be "hair splitting". The simple fact of the matter is that if an associative array is indexed by BINARY_INTEGER or one of its subtypes, there are upper and lower bounds (minimum and maximum values) that can be used as index values. So what if those bounds are defined, indirectly, through the use of BINARY_INTEGER?

3. I love having my book quoted at me. I conclude two things from this quote: (a) I need to change the wording. Associative arrays are unbounded only from a practical, not theoretical standpoint. It is precisely from a theoretical perspective that they are bounded; and (b) we need to distinguish between the idea of an upper bound on the number of elements in a collection and on the index values of that collection. With associative arrays, there is an upper bound on the integer values that can be used as index values, but there is no practical bound on the number of elements that can be defined in the collection.

Your thoughts as we play the last few quizzes of 2010?

Happy new year,
Steven