In Automation Designer I want to pass a variable to the export query so that I can specify that only records after a specified date be exported. Is there a way to pass a variable to the sql query? I’ve tried setting a toad variable export_date and using :export_date in my sql. Doesn’t generate any errors but the value does not seem to be substituted.
Message was edited by: leonmcd
Sorry, there is no way to do that at this point. That’s not a bad idea though
(the way you were trying to do it with Toad Variables, I mean)
Maybe a “kludge” for now would be to place that value in a text file
and have Toad reference that as an external table so that you can read in and
thus use the value in a query ….
As a workaround, if you use Toad for Data Analysts – which is included in
all copies of Toad for Oracle – it has an Automation Feature which allows
you to build dynamic queries. You can define variables and use it to build
actions for exports to Excel or Access and then also do the Email delivery.
Here’s a good resource for getting started:
http://www.toadworld.com/BLOGS/tabid/67/EntryId/498/Automation-Variables-Automati
on-variables-using-datasets.aspx
I had thought of creating a table with the parameters I need then including them in the queries for the export. To run it I just edit my parameters table then run the Automation Script. Might still do that. It is at least doable until I find a more elegant way.
thanks,
I checked out Toad for Data Analysts. Has some good stuff but didn’t see any way to prompt for a variable.
thanks
I believe that’s possible, but we should continue the conversation on that
product community’s forum as it’s maintained by a different set of
folks.
Jeff
It’s interesting that you reached for bind (
syntax instead of subst (&)
syntax. Toad variables are specified with %variable%.
In any case, % specifies a LIKE condition. Mike and I have discussed the
potential use of Toad variables within Actions which have a SQL property since
their inception, because on the face of it they are obvious candidates. However
if we used a special, different syntax to support them it wouldn’t be intuitive
or easily surfaced in the GUI, one would have to just “know” about it. And we
would still have to deal with string literals. For all of these reasons we’ve
sided on letting people discover alternative ways to accomplish their goals,
which I see you have already done.
(p.s. by string literals I mean any special, different syntax we’d use to
specify Toad variables within a SQL statement could conflict with actual string
data in the query itself. So we’d have to have an “escape” syntax for them or
some other goofy mechanism. Probably best to just let SQL be SQL)
Does anybody know if this feature (ability to pass a variable to an export query) has been added? If so, what is the syntax? I have tried EVERYTHING.
variable to an export query
Please specify what do you mean exactly. If some command line demo is possible even better.
I have created a test app in Automation Designer that creates a variable, tests the variable (which it passes) and displays the variable in a message. Now I want to export the results of a query (e.g., SELECT * FROM MYTABLE WHERE NAME = %MYVAR%), but I can’t get it to recognize the variable. I don’t know if it is a matter of syntax or if this functionality is not available in T4O.
Toad doesn’t support that functionality, sorry.
Easiest workaround I can think of is to make an app that creates a view that includes your where clause, then make a 2nd app that exports the view. You could change your view on the fly instead of your variable.
Seems like this has come up a few times…I’ll see what I can do about parameter support in the “export dataset” action.
Unfortunately, I cannot send print screens of the test I created, but here’s what I did in my test…
Create a Variable action
Added a Create File action that contained the select stmt and the variable. This will write the query (and the contents of your variable) to a file.
(Eg. In the Contents section: SELECT * FROM MYTABLE WHERE NAME = %MYVAR%)
You could also put the main select statement in a variable as well, and then the Create File content would just have both variables back to back (%QUERY%%MYVAR%).
From: WetSprocket [mailto:bounce-WetSprocket@toadworld.com]
Sent: Tuesday, February 11, 2014 10:47 AM
Subject: RE: [Toad for Oracle - Discussion Forum] Automation Designer variables for data export
RE: Automation Designer variables for data export
Reply by WetSprocket
I have created a test app in Automation Designer that creates a variable, tests the variable (which it passes) and displays the variable in a message. Now I want to export the results of a
query (e.g., SELECT * FROM MYTABLE WHERE NAME = %MYVAR%), but I can’t get it to recognize the variable. I don’t know if it is a matter of syntax or if this functionality is not available in T4O.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
John and I had a discussion about this last week and I think that next beta should resolve this. The original poster in this thread (in March '11) mentioned they tried bind variables first. %VAR% is the syntax commonly used everywhere in Windows where you'd
want to use an environment variable, but as noted earlier in the thread that format interferes with Oracle's LIKE expression and trying to distinguish between LIKE and environment variable when %TEXT% is used would be a nightmare. The best solution seems to
be using bind or substitution variables.
For next beta: Write your query as you normally would and use bind or substitution variables for any dynamic values. When executing the SQL from the Editor the Variables dialog allows you to map a bind/subst var to an environment variable. The default behavior
is to use the value you enter literally, but if "Environment Variable" is checked then whatever you put into the value edit will be treated as an environment variable and that variable's value will be substituted in at execution. There is no need to type %PATH%
as the value, simply type PATH and set the datatype of the variable accordingly to VARCHAR2, etc. and Toad will handle the rest. For the purposes of Automation Designer on the Dataset tab where your query is present there's a button "Set Variables" that will
launch the variables editor window. Configure your bind/subst variables as you want. This kills two birds with one stone as it adds bind variable support to the Automation Designer's Export Dataset action as well as environment variable support.
If you are a beta user you can see this feature in Thursday's beta.
Michael
On 02/11/2014 10:46 AM, WetSprocket wrote:
Reply by WetSprocket
I have created a test app in Automation Designer that creates a variable, tests the variable (which it passes) and displays the variable in a message. Now I want to export the results of a query (e.g., SELECT * FROM MYTABLE WHERE NAME = %MYVAR%), but I
can't get it to recognize the variable. I don't know if it is a matter of syntax or if this functionality is not available in T4O.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - General notifications altogether.Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4259 / Virus Database: 3684/7045 - Release Date: 01/30/14
Internal Virus Database is out of date.
