One, Two, Many Implicit Conversions (5986)

A player brought to our attention some rather strange code in the 25 August quiz on TRUNC.

We asked you to use this block to determine your answer:

DECLARE
   multiplier   INTEGER := /*VALUE*/;
BEGIN
   FOR indx IN 1 .. 5
   LOOP
      DBMS_OUTPUT.put_line (
            'Trunc by '
         || NVL (TO_CHAR (multiplier * indx), 'NULL')
         || ' = '
         || TO_NUMBER (TRUNC (123456.123456, multiplier * indx)));
   END LOOP;
END;

And this is what he wrote:

"I just hope your construct is meant to illustrate "bad practice"? You have this line: "|| TO_NUMBER (TRUNC (123456.123456, multiplier * indx))". You are doing TO_NUMBER on a number which will implicitly convert the result of TRUNC to a string, which TO_NUMBER converts back. Then the result of TO_NUMBER is a number which you are concatenating to your output string, so that must also be an implicit conversion? Have I read it correctly or am I just not awake yet ? I guess it might be meant as a distraction or a way to force ORA-06502? Anyway - you may have your reasons, I just wanted to let you know in case it happened to be a typo and you meant TO_CHAR instead of TO_NUMBER..."

Well, I must admit: that was a typo. And a lucky typo....it did not affect the outcome of the quiz, because Oracle ending up "doing the right thing" for us through implicit conversions.

So I am going to change the quiz to use TO_CHAR. Thanks for pointing this out, Kim!

SF