The key objective of the quiz was to make sure you were aware that when you open a cursor variable, Oracle always performs a parse.
But we also scored as incorrect the choice that stated:
And two players objected. The procedure referenced is:
CREATE OR REPLACE PROCEDURE plch_use_cfl ( id_in IN plch_employees.employee_id%TYPE) IS l_employee plch_employees%ROWTYPE; BEGIN FOR rec IN (SELECT * FROM plch_employees WHERE employee_id = id_in) LOOP l_employee := rec; END LOOP; END; /The objections are as follows:
As usual, a choice composed of words instead of code leads to issues of interpretation. I thought I'd worded this choice so that it was clearly not true. And I still believe that. OK, my response:
Regarding the comments in (1), you are right that the choice is not worded correctly...to warrant marking the choice as correct. Both players correctly understand that a cursor FOR loop is optimized to fetch up to 100 rows at a time, instead of single rows. Even putting aside the issue of what "implicit query" means (more on that below), the optimizer does not re-write that code
Regarding "implicit query": yes, that was sloppy language. I should have been more, ahem, explicit. The following is what I had I meant:
I hope everyone will agree that this is false. That is not what the compiler does. OK, so then is my original formulation ambiguous and in need of re-scoring? I don't think so...because:
a. I expect that most players did interpret my phrase "implicit query" to be a SELECT...INTO. In other words, while not as precise as it should have been, it was precise enough.
b. But what if you wanted to interpret that phrase ("implicit query") literally? The second player writes that "As far as I am aware, there exists no such thing as 'implicit query'." Actually, I did a search on the phrase and found this:
MERGEstatement that does not explicitly include a
SELECTstatement uses an implicit query to retrieve rows to be modified.
So Oracle does define the implicit query, though not in a way that I had expected, and clearly not in a way that would lead to one interpreting this choice to be correct.