I have a SQL query that, among other things, looks for a row that contains a & as part of the value.
The problem I keep running into is that Toad wants to interpret this as a variable every time the query is executed, and I haven’t been able to figure out how to tell Toad that it’s not a variable - is there a way?
It should be noted that changing the value in the table to something else is not possible.
Are you connected to Oracle? The code is looking for the replacement literal & which can be contained between quotes and still be replaced.
Can you post your SQL statement so I can see where we are going wrong? I know that if you have a character in front of the & it won’t act as a bind. But not sure if that helps.
select * from a
where a.col like ‘a&’
Debbie
Hi Debbie,
Yes, I’m connected to Oracle. There’s not a character in front of the & other than a single quote to indicate that the value is a character. Here’s the snippet:
AND cp.promocode NOT IN
('3.', '=_', ':_', ':.', ').', '(.', '4<', 'C<', '%.', '#.', '$.', '*.', '<M', 'T-', '&.')
The offending bit is highlighted.
Debbie Peabody wrote:
Are you connected to Oracle? The code is looking for the replacement literal & which can be contained between quotes and still be replaced.
Can you post your SQL statement so I can see where we are going wrong? I know that if you have a character in front of the & it won’t act as a bind. But not sure if that helps.
select * from a
where a.col like ‘a&’
Debbie
(‘3.’, ‘=’, ':’, ‘:.’, ‘).’, ‘(.’, ‘4<’, ‘C<’, ‘%.’, ‘#.’, ‘$.’, ‘*.’, ‘<M’, ‘T-’, Trim(’ &.’))
Debbie
I entered CR84,227 to fix. In the meantime try this work around. For some reason a space in front of the & prevents the bind window from popping up. I added a trim around it. Silly, but it seems to work.
Hi Debbie,
Hate to resurrect this, but this behavior is still broken in TDA 3.1 - ran into it today.
I know you had said a CR was entered, so I didn’t know if it got fixed, and then broken in a later build or what.
Debbie Peabody wrote:
(‘3.’, ‘=’, ':’, ‘:.’, ‘).’, ‘(.’, ‘4<’, ‘C<’, ‘%.’, ‘#.’, ‘$.’, ‘*.’, ‘<M’, ‘T-’, Trim(’ &.’))
Debbie
I entered CR84,227 to fix. In the meantime try this work around. For some reason a space in front of the & prevents the bind window from popping up. I added a trim around it. Silly, but it seems to work.
Select * from address a
where a.ADDRESS NOT IN
(‘3.’, ‘=’, ':’, ‘:.’, ‘).’, ‘(.’, ‘4<’, ‘C<’, ‘%.’, ‘#.’, ‘$.’, ‘*.’, ‘<M’, ‘T-’, ‘&.’);
What version of TDA are you using? What type of connection. And what is the statement that has the issue. We may have found a new wringle to the issue.
Debbie
THe original CR is marked fixed. I took the original test SQL out of the CR and it works for me using an Oracle connection in TDA 3.1 and 3.2.
Here is my test SQL.
Hi Debbie,
I'm using TDA 3.1 and and Oracle connection, and it's the same type of query - here's the exact syntax, but it's essentially the same as example I originally gave.
(CASE
WHEN EXISTS (SELECT NULL FROM I_C_R CR WHERE CR.ACCTCORP=C.ACCTCORP AND CR.HOUSE=C.HOUSE AND C.CUST=CR.CUST
AND CR.SERV IN ('^J','1I','4Y','8H','D0','FH','HC','HL','VL','Z!','&4','<#6','<#8','^K','.F','E!2'))
THEN 1
ELSE 0
END)
Upon execution, TDA prompts for a bind variable for &4
Debbie Peabody wrote:
Select * from address a
where a.ADDRESS NOT IN
('3.', '=', ':', ':.', ').', '(.', '4<', 'C<', '%.', '#.', '$.', '*.', '<M', 'T-', '&.');
What version of TDA are you using? What type of connection. And what is the statement that has the issue. We may have found a new wringle to the issue.
Debbie
THe original CR is marked fixed. I took the original test SQL out of the CR and it works for me using an Oracle connection in TDA 3.1 and 3.2.
Here is my test SQL.
This is actually becoming quite an issue.
Would it be possible to add support to TDA for the SQL*Plus commands like SET DEFINE OFF/ON or SET ESCAPE OFF/ON, etc. when using Oracle as the connection type?
Toad for Oracle already supports most of these, plus has an option to turn off substitution variables.
We bundle a small exe for support of SQLPLus. It is called QuestScriptRunner. To use, go to the run button. On the drop down you should see “Run SQL Script in External App”. This will launch and run the current script in the app that supports SQLPlus.
Debbie
Hi Debbie,
QSR doesn’t appear to be able to fine my TNSNAMES.ORA file (no connections listed in the drop down) despite the PATH variable being set. So it won’t run the script.
I still don’t understand why if the behavior was fixed, it’s not working that way for me. Additionally, if the QSR that TDA uses is the same as the QSR bundled with Toad for Oracle, we won’t be able to use it - the one bundled with Toad for Oracle only supports .xls as an output format, which only supports up to ~65k rows - we regularly (on a daily basis) export 90k+ rows and even have trouble with TDA with doing that.
Does is QSR even supported with automation?
Debbie Peabody wrote:
We bundle a small exe for support of SQLPLus. It is called QuestScriptRunner. To use, go to the run button. On the drop down you should see “Run SQL Script in External App”. This will launch and run the current script in the app that supports SQLPlus.
Debbie
‘&’||‘4’ instead?
It seems to bypass the error but not sure if it finds the correct data.
Debbie
This issue with the use of the ‘&’ symbol is a different bug. I have entered CR101898 to address.
In the meantime do you get the same results if you use