Seeing results from running SQL query (1926)

In the 21 January quiz, I asked you to select the choices that would display "Ellison". Two of the choices were PL/SQL blocks containing calls to DBMS_OUTPUT.PUT_LINE. Two were queries. One block and one query were scored as correct. I received the following objections.

1. "There were () at the end of function invocation. PL/SQL syntax would not allow that."

2."I think it is wrong as you actually ask for code that displays the given text and not which query only runs fine. Therefore only Option 2 is right and not Option 4 as it only selects something but has no attached DBMS_OUTPUT.PUT_LINE statement."

3. "You don't have semicolons at the end of the SQL query. Is it supposed to be correct? It doesn't seem so - that's why I haven't chosen the answer #4 though in general I know it is valid."

My responses:

1. In fact, you can provide "()" after a function call, without any argument values, and Oracle will accept it.

2. When a query is executed it returns data, which you view on your screen - just as you would by running a block with calls to DBMS_OUTPUT.PUT_LINE. One of the queries returned "Ellison" and was scored as correct. I do not believe it should be necessary to provide specific instructions on how to interpret the effect of running an SQL statement.

3. Ah, very interesting! Many developers do  believe that the ";" character is part of the SQL language - it is not. It is simply the default terminating character in SQL*Plus (causing immediate execution of the statement).

In conclusion, I do not believe it is necessary to change the way this quiz was scored.

SF

Cursor FOR loop, automatic optimization and use of BULK COLLECT (1924)

In the 19 January 2011 quiz, you were asked to choose the block containing the most efficient looping through employee data. It was:

BEGIN
   FOR emp_rec IN (SELECT last_name FROM plch_employees)
   LOOP
      DBMS_OUTPUT.put_line (emp_rec.last_name);
   END LOOP;
END;
/

The primary lesson of the quiz is that in Oracle Database 10g and higher, with optimization set to at least level 2 (the default), the compiler optimizes cursor FOR loops so that execute at BULK COLLECT-like levels of performance.

Several players asked about documentation of this optimization feature. Others suggested that a better solution, not offered, is an explicit BULK COLLECT. I have invited them to post their comments here.

Regarding documentation, it looks like in the official Oracle documentation, specific optimizations like this are not detailed out. The feeling seems to be that such optimizations can change over time (to paraphrase: "In version 10 we optimize cursor FOR loops, in version 12 we no longer to do that or do it differently") and so providing a list of "promises" would not be helpful.

Regardless, Ask Tom discusses such optimizations here. I also cover it in my "Best of Oracle PL/SQL" training available here, as well as in Oracle PL/SQL Programming, the book.

So, dear players, please add your comments...