0 Replies Latest reply on Jun 8, 2018 4:15 PM by Bill Caulkins

    SQL Developer sometimes automatically left trims zeroes from content of bind variables

    Bill Caulkins

      Bind variables in SQL Developer 18.1 which begin with a zero and whose entire content is numeric ('0123') SOMETIMES has the leading zero trimmed off.

       

      Testing PL/SQL code within a SQL Developer worksheet I have the following block:

       

      DECLARE

        v_value VARCHAR2(255) := :i_value;

      BEGIN

        DBMS_OUTPUT.PUT_LINE('v_value is ' || v_value);

      END;

      /

       

      When executing this block I am prompted for the value of ":i_value" and I specify (without quotes) "0123"

       

      Here is output:

       

      v_value is 123

       

      This is a simplified example. In my "real" code I am passing bind variables to procedures/functions and "0123" is the value of a VARCHAR2 column within a row in a table.  I don't recall this ever happening until a week or two ago. With that said, it's not often I'm passing in bind variables with leading zeroes which could be interpreted as a number. If I specify a value for the bind variable as "0123K" (without quote) it works as expected. I can shut down and restart SQL Developer and the problem typically persists. As a work-around I'm hardcoding (with quotes) '0123' in place of the bind variable, which obviously works -- but am hoping that someone has an idea or that the bug might be fixed soon.

       

      Running SQL Developer 18.1.0.095 Build 095.1630

      Windows 7 Enterprise 64 bit

      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

      PL/SQL Release 12.1.0.2.0 - Production

      "CORE 12.1.0.2.0 Production"

      TNS for Linux: Version 12.1.0.2.0 - Production

      NLSRTL Version 12.1.0.2.0 - Production