tag:blogger.com,1999:blog-8677649049588007585.post2282136359385054739..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: FORALL, UPDATE and Reality-Doc Conflicts (6431)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-8677649049588007585.post-22091231677788313272011-08-11T14:29:05.051+01:002011-08-11T14:29:05.051+01:00Once again, players of the PL/SQL Challenge improv...Once again, players of the PL/SQL Challenge improve the PL/SQL doc set!<br /><br />Bryn Llewellyn followed up with me regarding this issue:<br /><br />You found a doc bug. Thanks for bringing it to my attention. I just filed bug 12861602 and set its Publish flag to Yes. You ought to be able to read it and to track its progress. Here's a copy of my account:<br />________________________________________________________________________________<br /><br />Find the account of the FORALL Statement in the 11.2 PL/SQL Language Reference in the Chapter "PL/SQL Language Elements". Then find the section "Restrictions on dml_statement". It starts thus:<br /><br /><<<br />If dml_statement is an UPDATE statement, its SET and WHERE clauses cannot reference the same collection. The workaround is to make a copy of the collection, and reference the original collection in the SET clause and the copy in the WHERE clause.<br />>><br /><br />This is quite simply wrong. There has never been such a restriction.<br /><br />I did some archeology and found that the account of this non-restriction was introduced in the 9.2 version of this book. Then it was worded thus:<br /><br /><<<br />Within a FORALL loop, you cannot refer to the same collection in both the SET clause and the WHERE clause of an UPDATE statement. You might need to make a second copy of the collection and refer to the new name in the WHERE clause.<br />>><br /><br />Earlier versions of the book (FORALL was new in 8.1) make no mention of this non-restriction.<br /><br />The wording was changed (without, it seems, checking the behavior), in the 11.2 version of this book.Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-10794779731207541672011-08-11T06:16:56.457+01:002011-08-11T06:16:56.457+01:00I usually don't complain about Reality-Doc Con...I usually don't complain about Reality-Doc Conflicts, because the usual answer is "but we learned something new" ;-)<br /><br />That's true, but what about using this feature in production? How far can I trust it to work? And when I get an error, what will Oracle support tell me: "Sorry, but didn't you read the docs, it is supposed not to work"?<br /><br />MarcusMarcushttp://matzberger.de/oraclenoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-1215518681233373472011-08-11T00:56:45.720+01:002011-08-11T00:56:45.720+01:00The script is, according to the Oracle doc, suppos...The script is, according to the Oracle doc, supposed to not work (I assume that's what "cannot" would imply in documentation). Instead, it works just fine.Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-46386285162551527732011-08-11T00:40:57.278+01:002011-08-11T00:40:57.278+01:00So was that script supposed to produce an error?
W...So was that script supposed to produce an error?<br />Worked on for me on 11.2.0.1.0Scott Wesleyhttps://www.blogger.com/profile/18106937181788036683noreply@blogger.com