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.

12 comments:

  1. I also blogged about this today - uncannily a topic I had drafted for a few weeks ;-)


    I've used this feature extensively in various flavours of 10g and never had any problems - can't comment on 11.

    ReplyDelete
  2. On my local machine, I can't get it to work because I have the Oracle 9 client installed, despite connecting to an Oracle 10 database. So it gets picked up by SQL*Plus or other client side tool as an error if there are an odd number of quotes. Works fine if I use the Oracle 10 SQL*Plus client on the server though.

    ReplyDelete
  3. crikeymo, we found that when using subquery factoring (with xyz as (select...)) on a 10g db but our BA still had an 8i client!

    His client just complained about "expecting SELECT" or something along those lines.

    Was something that came to mind when reading #2

    ReplyDelete
  4. I have the same issue, on my local machine it doesn't work using SQL*Plus release 9. If I run the script on the database server using SQL*Plus 10 it works fine.

    ReplyDelete
  5. I believe this issue was reported in Metalink as a bug, though I can't find it anymore.

    When using an older (i.e. 9i) client, then a string with an odd number of single quotes fails with an ora-01756 (quoted string not properly terminated).
    A string with an even number of single quotes will work as expected!

    ReplyDelete
  6. Re. the presence of the comma, I'd say it was fine, if you were referring to "the man" as the object, and not referring to someone with the title of "the man who didn't like goodbyes".

    If the former, you could rewrite the sentence: "The man, who didn't like goodbyes, said 'Hello.'", where the commas make perfect sense (at least to me, anyway!)

    ReplyDelete
  7. I believe this issue was reported in Metalink as a bug, though I can't find it anymore.

    When using an older (i.e. 9i) client, then a string with an odd number of single quotes fails with an ora-01756 (quoted string not properly terminated).
    A string with an even number of single quotes will work as expected!

    ReplyDelete
  8. No problems here, but 10 database and 10 and 11 clients, also Toad 11 beta works.

    About the comma, there are two issues with it The second one can change the meaning of the sentence, but the first on in 'Hello,' looks like half a sentence. So the speaker is not finished yet with his sentence. This comma should be a period?

    ReplyDelete
  9. Wow, I've used this with the pound symbol (#) a lot... but I never knew it did the paired delimiters. I got all of those wrong because of that. :(

    Learned something new and useful!

    ReplyDelete
  10. I've seen no problems with this mechanism, but I am using the 11gR2 client with 11gR1 and 11gR2 databases. Perhaps the Quiz Assumptions should be amended to include a statement that the client is the one included with the target version of the database.

    ReplyDelete
  11. Like Richard M, I also got a slew wrong because I didn't know paired delimiters would work.

    I'd have much preferred one single option on matched delimiters. But then, due to the gross markdown on my score, I will never, ever forget that matched delimiters are an option.

    ReplyDelete
  12. This is a first time the quiz addresses the feature I never have heard of. Thank you, Steven!

    ReplyDelete