The 31 March quiz tested your knowledge of the use of the "Month" format mask for TO_CHAR. We received the following comments and questions:
1. "ELSE 'Not Short and Not Long *'||v1||'*'||v2||'*' . answer: Not Short and Not Long *September*October * Why is the length 2*9 positions and not 10?"
2. "Hi, I'm referring to the daily quiz for 31 March 2011. This quiz was very interesting, testing the players' various areas of PL/SQL functions and data types. Unfortunately, in the post-quiz comment, the author only mentioned how the data format model affects the return value, but forget to talk about the importance of data types and implicit conversions.
"If v1 and v2 were declared as CHAR, then blank-padded comparison semantics would have been used, and the output would be 'Long and Sour'.
"On the comparison semantics: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements002.htm#sthref305
3. I have read the explanation but still don't know why it is like that. Am sure there will be a hot discussion on this issue. I personally feel it's a bug. Waiting for the discussion to start on the blog.
I have invited Jeff, the author of the quiz, to reply; we will also undoubtedly update the explanation after this conversation is finished.
The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
I was unaware of "Fill Mode" format. Thanks to Jeff and The Challenge for another new learning :)
ReplyDelete1. It's not 10 (the declared length of the variable) - if you think about it, how could the TO_CHAR function be aware of how long the variable its return value will be assigned to is?
ReplyDeleteAs I understand it, the TO_CHAR function would know how long the longest month name is - in fact, it would have to know the max length for each supported language.
September is the longest month name in English (and is even more important because that's the month in which I was born). Therefore, the length of the string produced by the 'Month' date format will be 9.
2. It's a good point - the comparison semantics of the CHAR datatype would make a difference.
3. It's definitely not a bug - it's a feature! Certainly, in the old days of fixed-width fonts, this behaviour was handy for formatting a text report - it made it easy to have the dates formatted consistently in columns.
Nowadays, with the prevalence of variable-width fonts, it's much less useful - and we're stuck with the need to add 'fm' to our date format strings to maintain backwards compatibility.
The third comment was mine. Thanks Jeff again for this explanation as I was still thinking what could be possible reasons behind implementing it like this.
ReplyDeleteI would like to see more quizzes on DATE datatype as I had encountered several issues with using this datatype incorrectly, may it be in new development or dealing with code written by someone else.
Also, I am thinking if there could be another section on The Challenge where we can have all these tips preferably categorized subject wise it would be of great help and a handy reference when we come across certain bugs in our code or stuck up with something.
Jeff,
ReplyDeleteI respect that you're right that the values are returned padded, but I still thought it was a pretty cheap distinction. Yeah, I learned from it, for the moment, but I'll never remember that I would need to trim these values.
"Cheap distinction"? Hey, it's just reality! We don't get to choose how Oracle implements its functionality; we just get to deal with the consequences to our code and to our users.
ReplyDeleteYes, it's unlikely that we will all remember about the padding - but at a minimum, when it does cross our rather we are all now more likely to quickly realize the source of the problem and add that "FM" modifier.
Ramesh, your suggestion about categorized tips is a fine one. We are working on "refactoring" quizzes into a library. It will happen over time, but definitely this year.
SF
Steven,
ReplyDeleteYour reply to my "cheap distinction" whine was on the money. So I'd have done a unit test for any program like this to make sure I had the syntax right. But no chance for that in the quiz (cuz I'm too proud to cheat). ((but not proud enough to put my name to this comment!)) :-)