Hi
I’ve found a small bug with bind variables.
If I run a query like this: select :a from dual;
and then set :a to be a number literal and make the value 1.5 (or any non-integer), I get: “1.5” is not a valid integer value.
I can work around this by setting :a to be a varchar2, but I thought I should report it.
Thanks
Dan
Hi,
Hm … cannot get it.
When Toad ask you for bind value, you have an option to choose bind value type…and you have choosed varchar2. From the same combo choose number and see the result…but be aware that this depends on your session NLS settings (decimal point definition).
P.S.
In mine case, when I set as VARCHAR2 bind value, I do not get “1.5” but pure 1.5:
Cheers!
Actually, we just got a support case in for this very issue. Our developer is out until Monday, and then we’ll address it, as there may be a reason why we cannot use a decimal, even when it’s set to a number data type. I’ll reply back to this post when we have more info about it. Thanks.
Thanks Dennis.
Damir I’m not sure what you’re saying. You have to choose ‘number’ to get the error. If you choose varchar2 then it works fine, but there are situations where you need the bind variable to be a number and you need that number to not be an integer.
Hi,
Bug is not related to dual and bind values but generally to toad session which is not following session values.
Here is an example of the same error without bind and dual:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=’.,’;
select 1.5 from dual;
returns:
1,5
But as script runs fine. Here is the result:
Session altered.
1.5
**1.5**
1 row selected.
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=’,.’;
select 1.5 from dual;
returns:
1,5
Session altered.
1.5
**1,5**
1 row selected.
Obviously when run as query, it returns same values and it should not. Value is not derived from session but from Windows registry setting. This may have something with Query thread … but I tried all and doesn’t helps.
Script as you see, works fine.
And for the end, I thought reading you first post that you get “1.5” and didn’t understand where you get " signs surrounded beside numbers.
Of course, Damir’s explanation is one possible reason that this could be happening. As it happens, there is also a bug in our code which is more likely what Dan has experienced.
Damir - I think you might misunderstand me. I never get the " signs in the results of the query. I only get them in the error message, which is: [Error] Execution (4: 11): ‘‘1.5’’ is not a valid integer value
all clear, I was just trying to explain what confused me…and maybe explicit anounce as error message and new line with content of error message would be more readable … but never mind.
Hello,
I think a I’ve found an other bug in Bind variable: the Options|Data grids|Data|Date format display and a Date type bind variable handling are not in conformity with each other.
If I change Date format from YMD to YDM and enter 2014.19.09 in the parameter window then Toad gives the following error, “2014.19.09 is not a valid date and time”. However, the display format of Dates datatypes is YDM in data grids.
@drbubo20
when you execute
select sysdate from dual
as query (Ctr+F9) and as a script (F5), I’m betting you get different output (format!).
And this is becase Dispaly Grid format differs from Oracle SQL*Plus session.
So format of bind variables must obey mentioned rules as well
Hope this helps.
This will be fixed in our first beta when we start up the cycle again.
Great - Thanks!