3 Replies Latest reply on May 23, 2014 2:04 PM by Kirk Fritsche

    bind variable type changes for select versus delete/update

    Kirk Fritsche

      SQL Developer Version 4.0.1.14

      Java(TM) Platform1.7.0_51
      Oracle IDE4.0.1.14.48

      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

       

       

      It appears that sql developer determines the type of a bind variable differently based on if the statement is a select versus a delete/update and the bind variable is >= 2^31.

      This is causing unexpected rows to be deleted.

       

      The following demonstrates the type changing.

      -- create a helper function

      create or replace function mytest( p_x varchar2 ) return varchar2 as

      begin

        dbms_output.put_line('{'||p_x||'}');

        return(1);

      end;

      -- create a table to select against

      create table x as select * from dual;

       

      -- run the following with 2147483647 and 2147483648

      select * from x where dummy = mytest(dump(:test));

      -- returns the following

      {Typ=1 Len=10: 50,49,52,55,52,56,51,54,52,55}

      {Typ=1 Len=10: 50,49,52,55,52,56,51,54,52,56}

      -- repeat with a delete statement

      delete from x where dummy = mytest(dump(:test));

      -- returns the following (update returns the same)

      {Typ=2 Len=6: 197,22,48,49,37,48}

      {Typ=2 Len=1: 128}

       

       

       

      The following demonstrates how a delete is incorrect

       

      create table valid_tab(value_column varchar2(2000));

      insert into valid_tab values('0');

      insert into valid_tab values('2147483647');

      -- use 2147483647 as the bind variable

      delete from valid_tab where value_column != to_char(:val);

      select * from valid_tab;

      -- returns the expected output

      2147483647

       

      create table invalid_tab(value_column varchar2(2000));

      insert into invalid_tab values('0');

      insert into invalid_tab values('00');

      insert into invalid_tab values('2147483648');

      -- use 2147483648 as the bind variable

      delete from invalid_tab where value_column != to_char(:val);

      select * from invalid_tab;

      -- very unexpected output

      0

       

      I've searched but could not find anything.

      Is this a known bug and is there a workaround?

        • 1. Re: bind variable type changes for select versus delete/update
          Gary Graham-Oracle

          Hi Kirk,

          In attempting to follow your (somewhat incomplete) test case on 4.0.1 against an 11.2.0.1 db, I did not get the same results you got.  However, I believe you either had some issue with defining the bind variable correctly, or else may be hitting this published bug:

          Bug 17758393 - SQL DEVELOPER IS CONVERTING BIND VARIABLE TYPE FROM VARCHAR TO NUMBER

           

          Setting up the function mytest, and the tables x, valid_test, invalid_test as you did, this is what I got:

          set serveroutput on 1000000;

          var test varchar2(2000);

          exec :test := '2147483647'

          select * from x where dummy = mytest(dump(:test));

          exec :test := '2147483648'

          select * from x where dummy = mytest(dump(:test));

          yields your result...

          anonymous block completed

          no rows selected

          {Typ=1 Len=10: 50,49,52,55,52,56,51,54,52,55}

           

          anonymous block completed

          no rows selected

          {Typ=1 Len=10: 50,49,52,55,52,56,51,54,52,56}

          whereas

          exec :test := '2147483647'

          delete from x where dummy = mytest(dump(:test));

          exec :test := '2147483648'

          delete from x where dummy = mytest(dump(:test));

          yields what you expected to get but did not...

          anonymous block completed

          0 rows deleted.

          {Typ=1 Len=10: 50,49,52,55,52,56,51,54,52,55}

           

          anonymous block completed

          0 rows deleted.

          {Typ=1 Len=10: 50,49,52,55,52,56,51,54,52,56}

          and, finally

          exec :test := '2147483647'

          delete from valid_tab where value_column != to_char(:test);

          select * from valid_tab;

          exec :test := '2147483648'

          delete from invalid_tab where value_column != to_char(:test);

          select * from invalid_tab;

          yields, again, what you expected to get but did not...

          anonymous block completed

          1 rows deleted.

          VALUE_COLUMN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

          ----------------------------------------------------------------------------------------------------------- ...

          2147483647                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

           

          anonymous block completed

          2 rows deleted.

          VALUE_COLUMN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  ----------------------------------------------------------------------------------------------------------- ...

          2147483648

          I ran this a couple of different times. Once with the DDL and inserts in a shared worksheet (with a commit at the end), then running the select and delete bits in separate unshared worksheets, then finally re-running the select and delete bits back in the original shared worksheet.  I used your code with the exception or recycling the :test bind variable in the spots you reference a different :val bind variable name.

           

          Crossing my fingers I made no copy/paste errors posting everything back here.

           

          Hope this helps,

          Gary

          SQL Developer Team

          • 2. Re: bind variable type changes for select versus delete/update
            It appears that sql developer determines the type of a bind variable differently based on if the statement is a select versus a delete/update and the bind variable is >= 2^31.

            I can reproduce that in 4.0.2.15 Build 15.21

            -- run the following with 2147483647 and 2147483648

            select * from x where dummy = mytest(dump(:test));

            -- returns the following

            {Typ=1 Len=10: 50,49,52,55,52,56,51,54,52,55}

            {Typ=1 Len=10: 50,49,52,55,52,56,51,54,52,56}

            -- repeat with a delete statement

            delete from x where dummy = mytest(dump(:test));

            -- returns the following (update returns the same)

            {Typ=2 Len=6: 197,22,48,49,37,48}

            {Typ=2 Len=1: 128}

            You can interpret that dump output using the info in the Oracle Call Interface doc

            http://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci03typ.htm#i423688

             

            The 'Typ=1' is a VARCHAR2 datatype and the 40, 49, etc corresponds to '2', '1' and so on. So the '55' at the end of the first is 47 and the '56' is 48 which correspond to the sequential values.

             

            The 'Typ=2' is a NUMBER dataype.  The first result (22,48,49,37,48) corresponds to '2147483647'. That is, it is a positive number so the digits have a one added to them (21 becomes 22, etc).

             

            The second result represents positive zero. And that is what you are seeing in your last result

            select * from invalid_tab;

            -- very unexpected output

            0

            So yes, Sql Developer appears to be using a different mechanism and probably has a bug.

             

            The similar queries in sql*plus do not show that issue:

            SQL> define test = 2147483647;
            SQL> select * from x where dummy = mytest(dump(:test));

            no rows selected

            {NULL}
            SQL> select * from x where dummy = mytest(dump(&test));
            old   1: select * from x where dummy = mytest(dump(&test))
            new   1: select * from x where dummy = mytest(dump(2147483647))

            no rows selected

            {Typ=2 Len=6: 197,22,48,49,37,48}
            SQL> define test = 2147483648;
            SQL> select * from x where dummy = mytest(dump(&test));
            old   1: select * from x where dummy = mytest(dump(&test))
            new   1: select * from x where dummy = mytest(dump(2147483648))

            no rows selected

            {Typ=2 Len=6: 197,22,48,49,37,49}
            SQL> delete  from x where dummy = mytest(dump(&test));
            old   1: delete  from x where dummy = mytest(dump(&test))
            new   1: delete  from x where dummy = mytest(dump(2147483648))
            {Typ=2 Len=6: 197,22,48,49,37,49}

            0 rows deleted.

            SQL>

            1 person found this helpful
            • 3. Re: bind variable type changes for select versus delete/update
              Kirk Fritsche

              Gary, that bug looks to be the issue which I am having, thanks for helping me identify it.

              I'll try to make my testcase cleaner prior to submission next time.