Same function, same argument, different outputs:
set serveroutput on;
declare
n number := 122.5;
c_plsql varchar2(1);
c_sql varchar2(1);
begin
c_plsql := chr(n);
select chr(n) into c_sql from dual;
dbms_output.put_line(c_plsql || ' ' || c_sql);
end;
/
{ z
PL/SQL procedure successfully completed.
Advertisement
Answer
In your case, chr() appears to be performing an implicit conversion to binary_float (IEEE 754 floating-point representation) as the datatype, as opposed to number (ANSI SQL NUMERIC). This results in different rounding results. I cannot duplicate your results on 19c unless I force the datatype, in which case I get the same results you do:
set serveroutput on;
declare
n1 number := 122.5;
n2 double precision(10) := 122.5;
n3 float := 122.5;
n4 binary_double := 122.5;
n5 binary_float := 122.5;
c_plsql varchar2(1);
c_sql varchar2(1); begin
c_plsql := chr(n1);
select chr(n1) into c_sql from dual;
dbms_output.put_line('number: ' || c_plsql || ' ' || c_sql);
c_plsql := chr(n2);
select chr(n2) into c_sql from dual;
dbms_output.put_line('double: ' || c_plsql || ' ' || c_sql);
c_plsql := chr(n3);
select chr(n3) into c_sql from dual;
dbms_output.put_line('float : ' || c_plsql || ' ' || c_sql);
c_plsql := chr(n4);
select chr(n4) into c_sql from dual;
dbms_output.put_line('bindbl: ' || c_plsql || ' ' || c_sql);
c_plsql := chr(n5);
select chr(n5) into c_sql from dual;
dbms_output.put_line('binflt: ' || c_plsql || ' ' || c_sql); end;
/
with output:
number: z z
double: z z
float : z z
bindbl: { z
binflt: { z
PL/SQL procedure successfully completed.
When using binary_double or binary_float, the chr() function implicitly rounds the value of n up in PL/SQL. In all cases of SQL, or when using number in PL/SQL, chr() truncates the decimal portion of the number, effectively rounding down.