Using Variables in a Toad Automation Query

[dewitte]

Hello! I am attempting to use a variable in a query in the Toad automation designer (v12.11).

In control, I use the Create Variable step and I put VAR_CP for variable and TEST for value. Scope is Local (current run).

The next step is a script step. Eventually, this will be an update such as:
Update MYTABLE set x=3 where MyValue=VAR_CP

I’ve tried a lot of different ways to do this:
MyValue=@VAR_CP
MyValue=:VAR_CP
MyValue=VAR_CP
MyValue=‘VAR_CP’
MyValue=’@VAR_CP’

etc - The error in all cases is Bind Variable not declared.

I just declared it - or thought I did.

Thank you!

[JohnDorlon]

Create a variable like this:

Now add a script action. Note the script is in a file – don’t use the “text” option where you just paste the script into the action. After adding the file, you can double-click in the “parameters” column to set parameters. VAR1 is the name of the variable in the first action (it’s hard to see but that’s what I typed in where it is selected in blue above.

My script looks like this. I am using &1 because this is the first parameter. If I had more than one of them, the 2nd would be referenced in the script as &2 , and so on.

Run both actions together by right-clicking the app, like this:

And my output file looks like this:

[dewitte]

Thank you - that worked great! I was using a query file anyway since my goal was to use the file in several automation scripts, but only change the parameter. Now that you pointed me in the right direction, I realize I don’t even need to create the variable, I can just put my literal into the parameter and it works.

[JohnDorlon]

You’re welcome. We use the &1, &2 notation because that way the scripts are compatible with SQL*plus.

For example you could do this to pass “99999” to the &1 variable in test.sql.

sqlplus username/password@DB @c:\test.sql 99999

[dewitte]

Is there likewise a way I can create a variable/parameter based on data from a table?

For instance. I want to pull transactions from a table for the highest date in the table, which may not be today.

Therefore, the variable would be something like:
select max(transdate) into HighestDate from transactions;

I could do that in the first automation step.

Then the query in the next step could be:
select * from transactions where transdate=&1

(where the parameter is the date variable from the previous step)

[dewitte]

Here is a possible solution (simple example):

VAR maxdate VARCHAR2(20);
BEGIN
SELECT MAX(transdate) INTO :maxdate FROM TRANSACTIONS;
END;
select * from TRANSACTIONS where TRANSDATE= TO_DATE(:maxdate,‘DD-MON-YYYY’);

This seems to work and can solve the problem. I was trying to use the Toad variables, but this works right in the SQL statement.

Again - thanks for your help!! I was working quite a while trying to get that syntax correct.

[JohnDorlon]

Well, assuming the “maxdate” is always going to be found in the database, you could always do that in a subquery, like this:

select *
from TRANSACTIONS
where TRANSDATE = (SELECT MAX(transdate) FROM TRANSACTIONS);

[JohnDorlon]

Edit: Oops, you weren't asking about Export Dataset. I'm going to leave this answer just incase it helps anyone else.

If your "variable" value just comes from the database, the best way to accomplish that is using a subquery, as I showed above...

But, to answer your question about variables....and I'm not 100% sure that this will work in Toad 12.11, but it works in the current version...

If you want to use variables in Export Dataset, you can do it like this:

[dewitte]

Yes, I realize it wasn’t a good example - I was more interested in how to do this functionally. In real life, it might be something other than MAX.

[dewitte]

That’s fantastic. Thank you - very helpful. I know a lot more about variables now than I knew this morning.

[JohnDorlon]

Edited to create multiple output files, one for each row of the driving query.

Subqueries are always best if you can get your data that way, but you can use the Query Iterator action to turn database values into variables that Toad can use. Query Iterator is on the "Control" tab of the automation designer.

Then I dropped an "Execute Script" action on top of (not below) the Query Iterator action (notice how the tree line comes off query iterator).

then when I add variables for the script I can reference the query iterator variable. (Note: I had to change the output from "clipboard" to "Discard output" option to avoid a clipboard error. That's fine since we have a "spool" command in the script anyway)

In the script, the first one is &1 like before.

When you run the action, the script will get executed one time for each row in the "Query Iterator" query, with the variable values corresponding to those rows.

Run as before...

and the output is too big to show in a screen shot but you get the idea.

[dewitte]

Cool. Since you put rownum=1, it’s only going to iterate once for SYS. If you left that where off, it would do all owners, correct?

[JohnDorlon]

Yeah I just edited it for multiple rows, look again.

[dewitte]

How about this - In automation designer, is it possible to set a variable based on the result of a query and then use that variable to determine which steps to run. For instance, you might run a query to get a simple result like a date and then you want to run Action Step - lowdate if it’s < 1/1/2017, but run Action Step - highdate if it’s > 12/31/2016.

I see a test variable step, and I could use that to put execute steps in either the then or else sections, so that would work. However, how do I get the database query result into the toad variable for use in the test variable step?

[JohnDorlon]

I was thinking you could do this

But it looks like "Test Variable" won't let you choose a Query Iterator variable. I can fix that for the next version but it won't help you with 12.11.

It's not exactly the use of a variable, but the example below runs script1 or script2 depending on the query result...

By the way, you can drop multiple actions in the "then" or "else" part.

[dewitte]

Ah - true or false based on the date. That would work in my case. I just moved up to 13.1, so I’m closer.

I’ll try it out. Thanks for all of your help.

[JohnDorlon]

Great. If you need anything else, let me know.