When I run a query in the editor window using bind variables (eg. “:v_num”) on Toad v10.0.0.41, if the query is badly formed then when the “enter the variable values” window appears, the bind variables are not always listed. No syntax error is produced.
I assume that the syntax check is being done after the variables have been assigned values, but you can’t get to this stage if the list of variables doesn’t appear!
Eg:
with my_tab1 as (select * from dual
where :fred != 1)
my_tab2 as (select * from dual, my_tab10 where :bob =1),
select :george from my_tab2;
Corrected query (which works):
with my_tab1 as (select * from dual
where :fred != 1),
my_tab2 as (select * from dual, my_tab1 where :bob =1)
select :george from my_tab2;
There are some differences behind the scenes between 9.7 and 10 with respect to bind variables. As a result you aren’t seeing the variables correctly with malformed SQL as you note. I’ve added a check in there to verify that the query is valid and if it’s not you will receive the same ORA error that you would have gotten in 9.7 after entering variable values and attempting to execute. So the behavior will still be different, but now there is an indication that your query needs some work before we can proceed. You’ll be getting the ORA error before entering bind variable values in this case. This fix is not in the patch.
Ok, I can see that there are some differences looks-wise between 9.7 and 10, but what I don’t understand is why Toad v10 recognises that yes, there are bind variables in the query but then can’t display them in the list?
If it’s clever enough to work out that it needs to pop the bind variable box up, shouldn’t it also be clever enough to work out what those bind variables are, regardless of malformed sql or not?
As it is, I’ve since discovered that you can still press the OK button, and it’ll then pop up the underlying syntax error.
It uses 2 different techniques for that functionality now whereas only 1 was used in 9.7. We have a routine that checks for variables. It’s quick and dirty, but effective. Our query component will tell us when bind variables are present and then we add additional checks to look for substitution variables. If that routine returns True then we know that we need to show the variables editor. When variables are found, 9.7 would use that same logic to locate and display the variables. Toad 10 uses the parser DLL to provide the list of bind variables and if it can’t parse the SQL because it’s malformed then we won’t get a correct listing. We use the parser DLL because it provides additional features that we may need going forward. Currently we are only using the character offset to highlight variables in the source in the variable editor, but there is also a need to handle PL/SQL variables as binds when executing SQL that’s embedded in PL/SQL. For this the parser is the only option. We don’t support this yet, but another thread on the toadbeta list pertains to this.
After fixing the issue the effect for you should be very similar. In 9.7 you could enter variables and then fix your query, but Toad 10.2 will require that you fix your query first and then enter variables. In both cases you need to fix the query and enter variables, but the order of those required tasks is reversed for 10.2.
Ok, makes sense now, thanks! So what will the user see in v10 for the malformed query then? Just the syntax error, or will the bind variable box still appear?
Sorry, I meant “After the issue is fixed, what will we see in (the patched) v10?” - I’m just curious as to whether we’ll just get the syntax error or whether the box will display and then the syntax error will occur?
That’s just it. In the patched version you will see what you see now because this wasn’t an issue fixed for the patch. In the next full blown version you will see an ORA error when you attempt to execute SQL with syntax errors. You will receive this error before you see the bind variables window. This is not in the patched version that will be available soon.
laughs - so much for trying to make myself crystal clear! I was trying to ask about what we’ll see in “whatever v10 build that contains the fix” ie. not the next patched version.
Thank you for answering the question despite my lack of clarity. *{
Back to the original issue of missing Bind variables in the pop-up box, are we to assume that the issue will be resolved only when 10.2 is released?
Version 10.1.1.8 did not solve my little issue with this. It has to do with the placement of commented code in close proximity to a Bind variable. Also, placement of the ‘&’ substitution variable affects things.
For example, consider these two lines of code:
AND scheduled_date = :p_from1 and :p_from1 is not null
– &P_WHERE_EVENT
When the substitution variable is un-commented, all bind variables following the substitution variable are missing from the pop-up box, including those way down farther in the query.
Oh, the substitution variable on the commented line is still presented in the pop-up box. I can see arguements for that as well as those for not including it in the pop-up box. I prefer that commented substitution variables NOT appear in the pop-up box.
I’ll be following this thread because this bug forces me to run my queries in v9.7.x.
Your issue below is different than that of the original poster. The original
issue in this thread pertains to having SQL with syntax errors. That issue is
fixed for 10.5. I will look at your issue later today.
Message from: Stephen.Cohen_531
Back to the original issue of missing Bind variables in the pop-up box, are we
to assume that the issue will be resolved only when 10.2 is released?
Version 10.1.1.8 did not solve my little issue with this. It has to do with the
placement of commented code in close proximity to a Bind variable. Also,
placement of the ‘&’ substitution variable affects things.
For example, consider these two lines of code:
AND scheduled_date = :p_from1 and :p_from1 is not null
– &P_WHERE_EVENT
AND CASE_COURT_CODE = :P_COURT_CODE
AND CASE_LOCATION_CODE = :P_COURT_LOCATION_CODE
When the substitution variable is un-commented, all bind variables following the
substitution variable are missing from the pop-up box, including those way down
farther in the query.
Oh, the substitution variable on the commented line is still presented in the
pop-up box. I can see arguements for that as well as those for not including it
in the pop-up box. I prefer that commented substitution variables NOT appear in
the pop-up box.
I’ll be following this thread because this bug forces me to run my queries in
v9.7.x.
Can you send me an example of your bug offline? Your snippet posted the other
day isn’t enough to go on. I wrote an extremely small and simple query
that has a commented substitution variable preceded and followed by bind
variables and it works as expected. Please provide a complete query that
demonstrates your issue.
Are you getting an error message before the bind variables window displays? If
so, what does the message say? If it is an ORA error, what is the code?
