05 November 2010
The quote character (q) and the 4 November quiz (1604)
This quiz tested your knowledge of the new-to-10g feature of using the q character so that instead of doubling each single quote inside a string literal, you can specify your own delimiter character for the literal, and then use single quotes inside the string, thereby making it easier to read and manage dynamically constructed strings. I received several emails with highly varied responses to this topic, some indicating what I can only consider are bugs in this feature or in the way they were used when players tested the feature. I have had no problems with using the q character on both Oracle10g and Oracle11g databases. 1. "I loved today's question, I was not aware of the q function...and I could immediately use it today, so another victory for the Challenge!" 2. "Something strange happens related to this quiz, regardless of the choices presented. It looks like, in general, the alternative quoting mechanism does not work at all if the literal string contains a SINGLE QUOTE character as the first character of the literal value. This restriction IS NOT documented in the Oracle SQL Language reference, however it gives the following error: SQL>select q'*'Hello,' said the man, who didn't like goodbyes.*' 2 from dual; ERROR: ORA-01756: quoted string not properly terminated though, as per the documentation, this was supposed to work. Removing the first quote character and leaving all the alternative delimiter stuff in place makes things working. The case is the same for both SQL and PL/SQL statements. The documentation only says that: "...if the quote_delimiter appears in the text literal itself, ensure that it is not immediately followed by a single quotation mark." which IS NOT our case. This practically renders ALL the choices to this quiz as incorrect, except for the one that uses the classical quote delimiter, though I am sure that this WAS NOT your intention, you probably intended to check the knowledge about the "alternative quoting" feature, and not to make all of us stumble upon this documentation bug ... I dare say that: 1. Most of the players were not aware of this restriction regarding the first character in the literal. 2. It could be interesting to know what percent of people are using this mechanism at all in their daily work ... I am sure that many other players will comment on this quiz." 3. From Christian Rokitta: "I just published a blogpost on today's quiz subject" 4. "Thanks for bringing this topic up. I did not know that Oracle had introduced this ability. (Thus I got a very poor score in yesterday's quiz) I tried it out immediately - but your examples did not work on my 11g database (I know, the quiz assumes a 10g database, but features introduced in 10g should also be available an work in 11g also). Playing a bit around I found out that the user-defined delimiters works when there are a even number of quote signs in the string literal, but not when the number of quote signs is uneven, as in your examples. I wonder, is this a bug in 11g?" 5. "This is a new one for me, the Q mechanism. Had to quickly search for the documentation and made one mistake, the () I oversighted. But is the sentence correctly English? The comma should not be there?" Regarding #5: Hmmm, you know, I think you are right. I am used to putting a comma before and after the speaker of a quotation, but perhaps in this case the "who" should flow directly after the "man". I will check with my wife, who has a degree in English literature. I am puzzled by the reports of this functionality not working. Again, I did not experience any problems. Perhaps you could use the verification code on the Past Quizzes page and see if, indeed, my precise code in the choices are causing you errors. Let us all know what you find out.