PL/SQL Puzzle: when implicit conversions come calling

I posted the following puzzle on Twitter:

A quick little #PLSQL puzzle: I set serveroutput on. After running the code you see in the block below, what will be displayed on the screen?

Try it yourself before reading the rest of the post!

White space


so you do not immediately


see my answer. 


:-)

The output is:

10
1
9

I expect most of you got the first two right and maybe the third one wrong. Note also that the block does not fail with any kind of exception, such as VALUE_ERROR. The reason for all this can be summed up in one phrase: implicit conversion.

As noted by several people, this is a collection indexed by strings, not integers. Only associative arrays (INDEX BY) types support this. And that makes all the difference in this puzzle.

The value being used in the assignment of 100 to elements in the array is an integer (indx).

Since the index type is a string, the PL/SQL engine implicitly converts integers 1 through 10 yto strings "1", "2" ... "9", and finally "10". These are the actual index values used.

So certainly ten elements are added to the collection and so the count returned is 10.

But when it comes to FIRST and LAST, you have to understand how PL/SQL will determine the "lowest" defined index value and the "highest" defined index value.

For integer-indexed collections, it is clear: whichever number is lowest or highest.

But for a string-indexed collection, "lowest" and "highest" are determined by the character set ordering. And in this case "1" comes before "2", "2" before "3" ... "8" before "9".... but "10"? That is not greater than "9". It is greater than "1" and less than "2" as you can see by running this block:

DECLARE  
   TYPE t IS TABLE OF INTEGER  
      INDEX BY VARCHAR2 (3);  
  
   tt   t;  
 
   l_index VARCHAR2 (3); 
BEGIN  
   FOR indx IN 1 .. 10  
   LOOP  
      tt (indx) := 100;  
   END LOOP;  
  
   l_index := tt.FIRST; 
 
   WHILE l_index IS NOT NULL 
   LOOP 
      DBMS_OUTPUT.put_line (l_index); 
      l_index := tt.NEXT (l_index); 
   END LOOP;  
END; 

1
10
2
3
4
5
6
7
8
9

Sometimes PL/SQL makes it too, too easy to work with compatible datatypes. :-)

Check out my LiveSQL script demonstrating all of this.

Also, Hasan Alizada was kind enough to offer this screenshot on Twitter offering a very nice explanation. Thanks, Hasan!