ORDER BY needed on SELECT for answers to be correct? (2243)

The 27 April quiz focused on associative arrays and creative use of string indexing to reorder data in a collection. It prompted a flurry of emails, most of which were along these lines:

"I think the bulk select should have an order by to ensure that one of the choices is either always right or always wrong. Without the order by, I don't think you can assume the code will work in a certain way.."

"The way I read this question is which block of code _will_ show the two dates from the hire column in the order shown. The query in the pl/sql block does not contain an order by clause - there is no way to guarantee the order of the rows selected by the query. None of the blocks of code can be guaranteed to show the rows in the order shown in the question. Had the question been phrased which block of code _may_ show the specified results, then some of the answers would be correct."

"The options are effectively: 1) order by to_char(hire_date, 'J') 2) order by to_char(hire_date, 'DD-MON-RR') as NLS assumptions have a DD-MON-RR date format 3) order by to_char(hire_date, 'YYYYMMDD') 4) no order by With the date provided (inserted in the order that it is), options 1 and 3 are definitely correct, option 2 is definitely incorrect and option 4 is technically indeterminate but practically incorrect given the insert order and no indexing (based on my prior experience - ie didn't test to confirm). While it is not a actual requirement, none of the options perform a true date sort (assuming BC data, although option 1 needs pre 7-Nov-1975 BC data)."

"While in this particular case it is almost 100% sure that performing the BULK SELECT without an ORDER BY will place the 2 rows into the l_plch_employee associative array in the same order as the 2 rows were inserted into the plch_employees table, this is in general NOT ensured by SQL. Therefore, the quiz choice with AnswerID=6054, that is most probably meant to be an incorrect one, is not 100% decidable, except if you add for example an "ORDER BY employee_id" to the BULK SELECT. Just a remark for SQL-purists, that might object on this issue."

And that wasn't all! One player wrote:

"Today's quiz appeared to have no correct answer. All of these answers, except for the 3 answer, contain DECLARE...BEGIN...END blocks. These cannot be used within the BEGIN...END section of an anonymous block. The 3rd answer does nothing to specify the order of the hire dates, and is therefore inadequate."

Finally, a couple of players wrote to complain about the sheer volume of code that had to be read to answer the quiz.

My responses:

1. Regarding the lack of ORDER BY: I'd actually rather invite answers from players first, before I offer my view on this. I am curious to see what you think.

2. Regarding the inclusion of DECLARE...BEGIN...END inside another BEGIN...END: actually, that is absolutely acceptable in PL/SQL. That is an example of a nested block. I generally avoid nested blocks in my code and instead create a nested or local subprogram (procedure or function). This means that instead of the following:

BEGIN
   some_code;

   DECLARE
      l_var   NUMBER;
   BEGIN
      l_var := 100;
   END;

   more_code;
END;

I would write the following:

DECLARE
   PROCEDURE set_l_var
   IS
      l_var   NUMBER;
   BEGIN
      l_var := 100;
   END;
BEGIN
   some_code;

   set_l_var;

   more_code;
END;

I find this approach much more readable and easier to maintain.

3. Regarding volume of code: generally I try to avoid lots of code repetition in the choices by isolating the common parts, putting them in the question and just including a comment in the question code that should be replaced by the code in the choices. In this case, however, three of them were very similar but the fourth was quite different, so I could not easily avoid the situation.

Looking forward to your thoughts....SF