Hello,
I encountered the following mysterious behavior. The following anonymous block
declare
function check_integer(p_num number)
return varchar2
is
begin
if
trunc(p_num) = p_num then
return 'Y';
else
return 'N';
end if;
end check_integer;
begin
dbms_output.put_line(check_integer(10.1));
end;
ends with
declare
function check_integer(p_num number)
return varchar2
is
begin
if
trunc(p_num) = p_num then
return 'Y';
else
return 'N';
end if;
end check_integer;
begin
dbms_output.put_line(check_integer(10.1));
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 15
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.
however in other clients/IDEs (I tried sqlplus, sqlcl, APEX SQL Workshop, TOAD, PL/SQL Developer and dbForge) it runs without any problem and produces the expected output like
N
Statement processed.
Please, why does it fail in SQL Developer (tested with 17.4 and 18.1 on 11g XE and 12.2 EE VM, always the same result)?
Thanks a lot,
Pavel
edit: elaborating with options Database => Advanced => Use Oracle Client (InstantClient 12.1) and Use OCI Thick driver does not seem to make any difference - behavior is consistent and the error above is always being raised
it looks like Windows(10) specific problem, on Linux it works as expected