5 Replies Latest reply on Aug 4, 2017 8:03 AM by flavioc

    Strange behavior of query with insert containing a bind variable

    flavioc

      Hi folks,

      SQLDeveloper Build 17.089.1709:

       

      I have a query like:

       

      insert into tmp_appe

      select a.*

      from table(function_returning_collection('01294560337')) a

      where id_isol = 1736666;

       

      This query executes correctly.

      But if I replace the string with a bind variable:

       

      insert into tmp_appe

      select a.*

      from table(function_returning_collection(:var)) a

      where id_isol = 1736666;

       

      the function returns a user defined application error as if the value var was incorrect (a query against this primary key is performed and apparently returns no_data_found).

       

      however if I perform the query containing just the select it works fine!

       

      select a.*

      from table(function_returning_collection(:var)) a

      where id_isol = 1736666;

       

      I guess there is a problem of type conversion with SQLDeveloper interpreting 01294560337 as a number and ignoring the leading zero, then converting it back into a string with a leading blank because the parameter of the function is declared as VARCHAR2, but I still don't understand why the SELECT works and the INSERT fails.

       

      Any ideas?

      Flavio