Oracle chr() diferent returns from SQL engine versus PL/SQL engine, why?

Question

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.

Answer 1

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.