SQL Developer bind variables prompt: force string values

Question

I'm trying to use SQL Developer bind variables prompt to speed up query execution, but I'm not getting the desired output: looks like the values I put in get converted in number.

Table description:

Nome               Null     Type         
------------------ -------- ------------ 
NUM                NOT NULL VARCHAR2(13) 
IS_OK                       NUMBER(1)

initial situation:

select NUM, IS_OK from numbers_table where NUM = cast(:dn as varchar2(13));

NUM         |IS_OK |
------------|------|
08331930078 |1     |

working updates:

1.

update numbers_table set IS_OK = 0 where NUM = 08331930078;

update numbers_table set IS_OK = 0 where NUM = '08331930078';

ouput:

'1 row updated'

non-working updates:

1.

update numbers_table set IS_OK = 0 where NUM = :dn;

update numbers_table set IS_OK = 0 where NUM = cast(:dn as varchar2(13));

output:

'0 rows updated'

Don't know what else can I do to force the value being parsed as a string.

SQL Developer version 4.1.3.20

Answer 1

That's interesting, and looks like a bug. You don't actually need to cast, the value from the 'enter binds' window is a string anyway, so this works:

update numbers_table set IS_OK = 0 where NUM = :dn;

when the zero-padded string 08331930078 is entered in the dialog.

The cast is not needed but ought to still work. If you run as a script instead, with a defined bind variable, then both forms do work:

var dn varchar2(13);
exec :dn := '08331930078';
update numbers_table set IS_OK = 0 where NUM = :dn;
rollback;
update numbers_table set IS_OK = 0 where NUM = cast(:dn as varchar2(13));
rollback;

You get 1 row updated for both statements. Going back to running as a statement still prompts and still has the same (odd) behaviour even when the bind variable has been defined in a script in the same session.

Incidentally, when you do:

update numbers_table set IS_OK = 0 where NUM = 08331930078;

what you're actually doing, as you can see form the execution plan's predicate section, is:

update numbers_table set IS_OK = 0 where to_number(NUM) = 8331930078;

which will stop any index on the num column being used, and may result in unexpected results - in this case, if these are e.g. UK phone numbers you probably won't have the same value with and without the leading zero, but it's something to be wary of generally.