1

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
user8952391
  • 13
  • 5
  • Not in 21c, got z twice. Post v$version and NLS settings. – p3consulting Sep 17 '22 at 08:40
  • Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production "CORE 11.2.0.4.0 Production" TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production – user8952391 Sep 19 '22 at 13:37
  • NLS_LANGUAGE ENGLISH NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT yyyy.mm.dd hh24:mi:ss NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET WE8MSWIN1252 NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT YYYY.MM.DD HH24:MI:SS.FF NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE – user8952391 Sep 19 '22 at 13:38

1 Answers1

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.

pmdba
  • 6,457
  • 2
  • 6
  • 16