tag:blogger.com,1999:blog-8677649049588007585.post3545206128146497279..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: Typo in 15 September quiz - but no need to rescore (1383)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-8677649049588007585.post-84778319780118064822010-09-17T12:57:25.237+01:002010-09-17T12:57:25.237+01:00Excellent points, jhall62. I should make clear on ...Excellent 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.<br /><br />I 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."<br /><br />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.<br /><br />SFSteven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-36024951576233276322010-09-17T12:54:44.764+01:002010-09-17T12:54:44.764+01:00The quiz question does a great job of doing what i...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.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-81738220709496740092010-09-16T22:04:28.639+01:002010-09-16T22:04:28.639+01:00While the potential performance issues with dynami...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 <i>sql_statement</i> in the <i>PL/SQL Users Guide and Reference 10g Release 2</i> 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.<br /><br />I 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. <br /> <br />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.<br /><br />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.jhall62https://www.blogger.com/profile/10339038131928463003noreply@blogger.com