## 29 December 2010

### "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

1. My point is that the 'phrasing the question' wasn't quite precise and that's why I think you should accept that answer and give the points back :-)

2. I think re-scoring on point #1 was a bit of stretch, but that's probably because the first thing I thought of was the 2**32 limitation due to the reference to "integers". Too much word play?

3. My thoughts are that I agree with point 2. The limit is on the index type (in this case BINARY_INTEGER) and not on the associative array. There's only so much you can fit into 32 bits. Taking point 1 into account, I think we could come up with code to fill up more items in the AA than we could using integers.

4. OK, the point#1 was from me, and I really didn't think re-scoring was required. I wrote it to highlight that one could use this varchar2 workaround for supplying integer values, and it can hold a higher value than a 'binary_integer' can have.

This not really a bound of Associative Array, rather its a limitation of 'binary_integer'

SQL>declare

2 n binary_integer;

3 begin

4 n := 2147483648;

5 end;

6 /

declare

*

ERROR at line 1:

ORA-01426: numeric overflow

ORA-06512: at line 4

This way, even a varchar2 string has an upper bound on integers values. The maximum 'integer value' that a varchar2 string can hold is 32767 nines. i.e rpad('9',32767,'9')

Since Associative array can only be indexed by binary_integer or varchar2, and both these have upper bounds on integer values, the original scoring of the quiz choice is correct, IMO.

Cheers

5. I'm a little confused by my score, 75% since, under Choices and Explanations, it shows me having all correct answers.

6. I disagree with changing the scoring based on any of the arguments listed in the original post.

1. Even if the all numeric VARCHAR2 workaround is accepted as being an integer (a position with which I disagree) the range of possible integer values is still constrained. A VARCHAR2 may hold a maximum of 32767 bytes; therefore, the range of possible "integer" index values is -(10**32767-1) to 10**32768-1.

2. Regardless of the sources of any limitations, the possible integer index values of an associative array are constrained.

3. Imprecise or incorrect descriptions outside of the quiz do not affect the validity of choices about implementation and thus should not be accepted as a basis for changing the scoring.

7. binary_double_infinity is not itself an integer value, but it is the upper bound for the integer values and can be used as index value:

declare
type tt is table of boolean index by varchar2(3);
t tt;
d binary_double := binary_double_infinity;
begin
t(d) := null;
end;
/

8. Ok, I have three points, too. ;-)

1. "If you index by VARCHAR2, then the values used as index values are not integers; they are strings" - absolutely correct.
2. Do not re-score because of word-games and/or possible workarounds.
3. Try to avoid text-based quizzes as often as possible.

Happy New Year!

Markus

9. _Nikotin:

BINARY_DOUBLE_INFINITY is an IEEE-754 derived encoding treated as positive infinity in calculations. It is not the upper bound for integer values (infinity is an unbounded quantity, thus it is not a bound at all). Oracle returns "Inf" when converting BINARY_DOUBLE_INFINITY to a character type. Your example code demonstrates that associative arrays can be indexed by VARCHAR2 with index values derived from types that can be implicitly converted to VARCHAR2 but does not address the issue of integer index ranges.

10. I agree with jhall62 and Spoon on #1.
It shouldn't be rescored, I think..

11. Spoon said that "The maximum 'integer value' that a varchar2 string can hold is 32767 nines" and jhall62 said "the range of possible "integer" index values is -(10**32767-1) to 10**32768-1"

I don't think it precisely correct
SQL> declare
2 type ty is table of number index by varchar2(100);
3 tb ty;
4 begin
5 tb(12345678901234567890123456789012345678901234567890) := 1 ;
6 dbms_output.put_line (tb.first) ;
7 end;
8 /
12345678901234567890123456789012345678900000000000

PL/SQL procedure successfully completed.

When you type in the number with precision more than 39 digit (I don't understand why it isn't 38. Could anyone else explain?), It was, then, round to the maximum precision so it's possible to have only 39 nines and (32767-39) zeros

12. "it's possible to have only 39 nines and (32767-39) zeros" is still not correct too.

You can not type in the integer with more than 126 digit so the maximum should be (1e40-1)*10**(125-39)

SQL> declare
2 type tt is table of boolean index by varchar2(200);
3 t tt;
4 begin
5 t( (1e40-1)*10**(125-39) ) := null;
6 dbms_output.put_line ( t.first ) ;
7 end;
8 /
9.999999999999999999999999999999999999999000000000000000000000000000000000000000000000000000000E+125

PL/SQL procedure successfully completed.

SQL>

13. siamnobita,
SQL Reference:
"A number of type NUMBER can store a maximum of 38 digits of precision. If the literal requires more precision than provided by NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, then Oracle truncates the value. If the range of the literal exceeds the range supported by NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, then Oracle raises an error."
So it's possible to use at most 126 digits and only 38 of precision as literal.

14. siamnobita:

According to Oracle's documentation, "numbers with precision up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point," are guaranteed to be portable. Running the following code will demonstrate that Oracle keeps a maximum of 40 significant digits:

BEGIN
DBMS_OUTPUT.PUT_LINE(CAST(123456789112345678921234567893123456789412345678951234567896123456789712345678981234567899123456789012345678911234567892123456 AS VARCHAR2));
END;

My description of the range of possible "integer" values that can be represented by strings of VARCHAR2 digits was intended to demonstrate that the claimed ambiguity is irrelevant to the choice's validity since even the most generous interpretation still constrains the number of possible "integer" index values (an absurdly broad range, but still a finite set of values).