Toad World® Forums

Sqls using bind variables


Some suggestion using sqls with bind variables in sqlnav. (scan defines ON)

  1. When you specify variable as :variable , variable menu does not carry about format of the datatype, for example when you set numbers - you can input any string you want, but only numbers shoul e allowed.
  2. When I try to bind a date variable (choosing date as type) error occurs:
    Invalid variant type conversion.
    Other types works ok.

Regards Piter


I forgot: I type date in my default session format…


I’ve just tried this on my machine - it works! So, Piter, could you send me an example of what you are trying to execute, along with your Date and Time format from the Regional Settings and the date formats from the Preferences (under UI and Session). I’ll try to reproduce it here with your settings.



Hi Roman!
My settings from system:
date format : yyyy-MM-dd , date separator “-” :wink:
Sqlnav options:
Default date format for session: YYYYMMDD
Displayed format: YYYY-MM-DD

I have table with col date type.
then :
select * from test_tab where col1=:enter_date

in compo i choose type as a date and write: 20060601 or sysdate , it doesnt work.
When i write date as 2006-06-01 (displayed format) it works ok.
So input date format shouldn’t be the same as default session format??

Regards Piter.


Thanks Piter for the info. Actually, the date should be in the ‘short’ date format from your Regional Options. We even display a message about this in the Output window when you enter an invalid string. I don’t know why you get a different error. So, you must enter 2006-06-01 because it’s your system format, not because it’s the Displayed format - they just happen to be the same in your particular configuration.



Hi Roman!
You are right, of course system format is important for this. Unfortunetly message about improper date format is not displayed in my nav (and for other user in my firm). Maybe it would be nice to keep correspondence with default date format, specified in sqlnav preferences. Setting this parameter I could have sureness that i type right date format in all places in sqlnav, not only in clear sql and pl/sql.
The other alternative, could be that you display system date format in “define bind variables” window as hint afther choosing variable type.
Think about it.

Regards Piter


I agree that it’s important to keep consistency, particularly in the date formats everywhere. Maybe we have too many different formats, and maybe we don’t need some of the preferences. We just thought it’s good to have such flexibility. But you are right, enforcing yet another date format - the system format - is too much. We do need to think about it. Enforcing the Displayed Format when entering dates is one option, but the problem is that not all possible formats are suitable for input. Showing a hint or a mask is another option. We need to review this in the next release of SQL Navigator. Jaime, please raise a CR.



I wasn’t going to raise a CR…
But then I saw that big gun that Chuck now has pointed at us…
So I thought I’d better



Hi Jaime!
Chuck is back, not without any reason, my car has been stolen yesterday :frowning:


Sorry to hear this Piter… not good news at all !!!
I hope you had your insurance all paid up.



Yes i Had, but of course i won’t get all costs from insurance (remember I live in Poland). Shit happends…