The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.

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

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.

29 December 2010

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

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.