The following query, when posted into the export dataset , fails variable substitution (even after setting the mapping via (,x,) ). (I use '~' and not '&' due to &'s in my data)
select l.ship_name,
level,
l.L_DESCRIPTION L_NAME,
l.L_Location_ID Location_UID,
l.L_PARENT_ID Parent_Location_UID,
connect_by_isleaf leaf
from gdonnells.TMPK_LOCATION_EXP l
where l.ship_name = upper('~S')
and l.l_hidden = 0
connect by l_parent_id = prior l_location_id
start with l_parent_id is null;
The ~S is mapped to a query iterator %ShipQry.Name%, but when you run this it doesn't replace the ~S with the shipname, it just leaves it as ~S. How do I fix this?
Here is the message I receive.... (Changed variable to sn)
Not sure, but guessing that your "variable" is within quotes, so won't get expanded.
Also not sure why you're using variable substitution, and not referencing the query iterator variable directly in your query...
e.g.
select l.ship_name,
level,
l.L_DESCRIPTION L_NAME,
l.L_Location_ID Location_UID,
l.L_PARENT_ID Parent_Location_UID,
connect_by_isleaf leaf
from gdonnells.TMPK_LOCATION_EXP l
where l.ship_name = upper(%ShipQry.Name%)
and l.l_hidden = 0
connect by l_parent_id = prior l_location_id
start with l_parent_id is null;
Referencing the query iterator directly in the query also fails
The ShipQry will return a string with spaces in it (as in 'SHIP 4')
Direct Substitution (using &1 or in my case ~sn) would cause the query syntax and ship_name=upper(SHIP 4), which will result in invalid syntax, hence the variable must be single quoted: ...upper('~sn') to result in the correct SQL syntax.
And to add insult to injury, this query works just fine:
but following the exact same setup and syntax, the original query ion this post fails.
Query iterator variables should not be referenced directly in SQL. The correct technique is to create a bind or substitution variable in the SQL, and connect the bind/sub variable to the Query Iterator.
@gdonnells
It sounds like you are trying to use a substitution variable. Those can be a bit delicate here for reasons I won't go into now. Have you tried this as a bind variable?
Select...
from....
where l.ship_name = :x
then when you hit the (,x,) button, select Bind variable and select the query iterator that way.
here is the variable setup
Which version of Toad are you using?
This works for me in the 17.1 version.
Note the export dataset is nested under the query iterator, not just after it.
Notice there are no percent signs around the query iterator name.
Try selecting your query iterator from the dropdown.
John:
Thank you for all the feedback. I have tried all forms of substitution - they have all failed.
Toad: 17.1.717.3711 64-bit
Oracle 19C patched current (instant client 19.1 64-bit patched current)
Windows 10 64-bit patched current
I have tried what you did above and it fails as well.
Un-installed toad and re-installed from fresh download - Same results.
Also, when using the 'execute script' action, there is no way to pass variables. If I put them with the filename (as command line 'parameters'), I get a file not found. When I try any form of substitution - it fails. There is no variable popup screen that allows me to map an automation variable to a script variable. Very inconsistent behavior and very frustrating.
As you can see this is all nested under the query iterator
Right-click on the app on the left and choose "Export".
It will create a text file. If you email that file to me I'll take a look at it.
john.dorlon@quest.com
You can pass variables to a script if the script is a file (not text in side the script action)
Steps to do that are here: Using Variables in a Toad Automation Query
In that post I show how to do this with a "Create Variable" action but it just as easily could have been a Query Iterator.
I have a dropbox folder with some documents I've written. Look at the one called "Automation Designer - passing multiple parameters to a script"
https://www.dropbox.com/scl/fo/axtip564da0fbj6vclt1d/AOs3uxwQLCaJpKCBOJNsicU?rlkey=nfu8vsvxckrjo3g35sheflduv&st=4yhqdgdd&dl=0
