I have an automation script that executes a sql script that contains bind variables.
One of the bind variables has to contain a wildcard (%).
I keep getting an error message: ‘Invalid variable’ when the script executes.
This script creates a copy of a file that uses this variable, and that part is working (ie it creates a file for me with ‘Japan-B%’ as the file name (B% is the value passed to the bind variable).
However, this variable is also used in the WHERE clause of the sql and this is the part that seems to not be working.
When I turn on Verbose, I can see the messgage ‘Variable value = Invalid character’
Are wildcards not supported? Or am I doing something wrong?
Any ideas???
Toad for Data Analysts: 2.6.0.561
Running against an Oracle database.
select * from contact
where LAST_NAME like ‘&a%’
If you make a bind variable with name of ‘a’ in automation you can give any value to a, excluding the wild card. It will be inserted and replace the ‘&a’ above. Note: The single quotes are necessary in the SQL above if using a string and my use of single quotes in my sentence are not necessary and used only for clarity.
I just tested this but like I said you have to be using TDA 2.6 as support for this type of binding was not available in 2.5.
Debbie
Well that is a bug on my part. I have entered Cr73,036 “Cannot use ‘%’ character as bind var in Automation” to fix for the next release.
I have a work around. I am assuming you are using Oracle. This work around has to be used with TDA 2.6 only. In that release I added support for Oracle’s replacement symbol of ‘&’.
I you change your query to use the replacement symbol we can tack on the wild card character and bypass the error.
For example:
Thank you for the quick response. I’ll try your workaround. I am running 2.6.
CR73076 for this issue has been fixed and will be in TDA 2.7. It missed today’s Beta posting but you can check the next Beta if you like.
Cheers,
Debbie