SQLDeveloper 4.2 problem with some bind variables values

I just found out that SQLDeveloper version 4.2.0.17.089 (tested on windows 10) might execute SQL or PL/SQL code containing bind variables with wrong argument values when such values are strings (VARCHAR2) containing just digits with leading zeros (i.e. international phone numbers, VAT codes, UPC codes...) entered at the bind variables prompt.

This seems to happen only with PL/SQL code or certain SQL containing statements like INSERT, not pure SELECTs.

The practical consequences of this problem range from statements ending with unexpected errors or executing a block of PL/SQL with wrong parameter values which could lead to a variety of anomalies like inserting, deleting or updating the wrong rows.

You can easily see the problem by yourself executing the following sample code:

create or replace function function_returning_collection(
   p_arg in varchar2)
   return ORA_MINING_VARCHAR2_NT
as
   l_str_tab       ORA_MINING_VARCHAR2_NT;
begin

   select str
    bulk collect into l_str_tab
    from(
      select '01234567' as str
        from dual
       union all
      select '31234567' as str
        from dual
      union all
      select '01234567' as str
        from dual)
    where str = p_arg;
   
   return l_str_tab; 
end;

create table test_bind (str varchar2(255));

insert into test_bind
select * from table(function_returning_collection(:var));

Enter 01234567 at SQLDeveloper's prompt and it will say "0 rows inserted", then execute just the SELECT portion of the query and it will return two rows instead.

Embedding the number within tick marks at the prompt won't fix the problem, SQLDeveloper is clearly taking the input value verbatim as a string.

I believe the only clean way to fix this in a future release is to allow the user to specify the data type being entered at the prompt, because looking at SQLDeveloper's statement log it's clear that SQLDeveloper is trimming the leading zero and then, owing to the implicit conversion, adding a leading blank, which transforms the initial "01234567" into a "_1234567" ( where "_" represents the blank character).

Note also that SQLDeveloper's online help makes no mistery of this "feature":

Execute Statement executes the statement at the mouse pointer in the Enter SQL Statement box. The SQL statements can include bind variables and substitution variables of type VARCHAR2 (although in most cases, VARCHAR2 is automatically converted internally to NUMBER if necessary);