In the 15 September regarding the use of FORALL with more than one DML statement, two of the incorrect choices were even
more incorrect than I had originally intended. They both ended with this line:
WHERE l_nums (indx);
but they should have been:
WHERE custnum = l_nums (indx);
This typo does not affect the scoring of the quiz, but I did not intend it to look like this. So I hereby award a prize of an O'Reilly ebook to Frank Schmitt for pointing out this typo. I will fix the text in the choices to reflect my original intention.
While the potential performance issues with dynamic PL/SQL blocks within FORALL statements are sufficient to discourage their use, there is an even better reason not to use them: the use of dynamic PL/SQL with FORALL depends on behavior that is outside of documented capabilities. Oracle’s PL/SQL documentation explicitly states that FORALL works with static and dynamic DML statements; it does not mention PL/SQL blocks. The description of sql_statement in the PL/SQL Users Guide and Reference 10g Release 2 is “A static, such as UPDATE or DELETE, or dynamic (EXECUTE IMMEDIATE) DML statement that references collection elements in the VALUES or WHERE clauses.” Given the level of detail provided, it is very unlikely that a more general capability was intended.
ReplyDeleteI do not argue that dynamic PL/SQL blocks won’t work with FORALL (even dynamic DDL happens work), only that it should be considered an unsupported accident of implementation and as such should not be depended upon. The issue of undocumented behavior has come up before in the quiz discussions and the usual response has been to advocate running some test code to confirm behavior. When dealing with unclear documentation, I agree that running some test cases is a reasonable approach to clarifying one’s understanding; however, no number of test cases can demonstrate that behavior contrary to the documentation is intended. An inability to perform as documented would be considered a defect. Failure to report an attempt to use a feature outside of its documented capabilities should also be considered a defect.
Quiz answers should not depend on behavior that is contrary to that described in Oracle’s documentation; such behavior could change with any patch. On more than one occasion I have had to modify legacy code that once worked but then failed after an Oracle patch updated the system to enforce documented behavior. Which question should be the basis of determining a quiz’s correct responses, “does it work?” or “is it valid PL/SQL?” If the latter, then choices that depend on undocumented behavior should not be deemed correct since such reliance is not valid. Accepting responses that happen to work reduces an affected quiz to a trivia contest or a test of one’s ability to quickly enter and run the test code.
I am not requesting a rescore. Although I was fully aware that dynamic SQL DML statements can be used with FORALL, I rejected the choice that was presented because a PL/SQL block is not a DML statement.
The quiz question does a great job of doing what it should do - educate on what PL/SQL is capable of. As long as the answer adds the caveat that a particular answer is not good practice (even though it happens to work), it has educational value. In this case, it's made me aware of this behaviour, while at the same time making me aware that it doesn't seem to be supported by the Oracle docs, which is good to know.
ReplyDeleteExcellent points, jhall62. I should make clear on that choice that this is an undocumented feature. I certainly make it pretty clear that there is no reason to use it.
ReplyDeleteI am not going to take your advice in this regard though: "Quiz answers should not depend on behavior that is contrary to that described in Oracle’s documentation; such behavior could change with any patch."
You are right that workarounds and undoc'd behavior can change. But one objective I have for the quiz is to encourage a sense of adventure and exploration of the PL/SQL language. I am not going to constrain myself to what is the in the docs, esp. because they often leave much to be desired.
SF