I ran into an interesting bug in some code today due to a math error which caused CHR() to be called with non-integer input values. According to the Oracle documentation, this is perfectly acceptable; but it doesn’t make much sense. Unfortunately, the documentation doesn’t specify what should happen for such inputs. This ambiguity is the cause of the surprise.
Furthermore, the functionality is different based on where the function is invoked.
Within SQL, CHR(x) = CHR(FLOOR(x))
But within PL/SQL, Prior to 12c CHR(x) = CHR(ROUND(x))
In 12c, PL/SQL and SQL are consistent and CHR(x) = CHR(FLOOR(x))
Here are some examples to illustrate.
SQL> connect system@db_ver_12c Enter password: Connected. SQL> set serveroutput on SQL> SELECT CHR(65), CHR(65.5), CHR(66), CHR(66.5) FROM DUAL; C C C C - - - - A A B B SQL> BEGIN 2 DBMS_OUTPUT.put_line(CHR(65) || ' ' || CHR(65.5) || ' ' || CHR(66) || ' ' || CHR(66.5)); 3 END; 4 / A A B B PL/SQL procedure successfully completed. SQL> connect system@db_ver_11g Enter password: Connected. SQL> set serveroutput on SQL> SELECT CHR(65), CHR(65.5), CHR(66), CHR(66.5) FROM DUAL; C C C C - - - - A A B B SQL> BEGIN 2 DBMS_OUTPUT.put_line(CHR(65) || ' ' || CHR(65.5) || ' ' || CHR(66) || ' ' || CHR(66.5)); 3 END; 4 / A B B C
Note in 11g the SQL and PL/SQL produce different results. I think the 12c functionality is what should be considered correct; but in this case there was some legacy code relying on the old rounding functionality to correct a math error.
The silver-lining in this story is that identifying the CHR() functionality change highlighted the math error and; once the math was corrected, the fix didn’t depend on either FLOOR or ROUND because only integer values would be provided as input.