Toad for Oracle Query Iterator

[toad.user]

I'm trying to get a very basic loop working to insert some records into a table. I've worked with Toad Data Point for a number of years, but this doesn't appear to use the colon or pound sign variables the way TDP does.

My problem is this. In my query iterator I have a simple select to return a list of values. Then I have a child action that is properly nested under it. In that I have an insert from select statement and I need to pass the output of the list to the insert as an input parameter.

I am using the syntax the query iterator shows %loopname.column%. However, the sql statement will err out if I don't put quotes around the value because it's a varchar value. The loop runs, but nothing executes properly and there's no log data to show what the rendered query looks like. I suspect the rendered variable isn't a valid value and so the select portion of the insert is returning no values.

I tried single quotes, double quotes, prompted bind vars (e.g. &1), pound signs and colons, but nothing works. I also don't see an obvious way to use a file and set the parameter on that file. When I try to use a sql file rather than the text option and add a parameter it treats the parameter as if it's another file and it doesn't appear to recognize what is in the file. For something this basic it seems to be more complicated than it should be.

I've read over several sources of Toad documentation and nothing clearly explains this. Found no videos either. Is there a correct procedure to use the query iterator to execute another query?

Thx

[Gary.Jerep]

One thing that may help here is that there IS a log created each time that you run one of your automation apps, and there IS a task that generates a log message. See snap below. At the very least, you'll be able to confirm if you're picking up the correct values from your iteration loop, and if you're assembling the correct syntax for your SQL. Hope this helps you out.

[JohnDorlon]

If the query iterator and the insert statement are in the same connection, then you can do this all in one SQL and don't need the query iterator at all.

Insert into table1
select * from table2 
where column in (select column from table3);

If the query iterator is a different connection than your script, then you are close, but you have to put your SQL in a file and pass the query iterator value as a parameter to the script. Like this:

Note the name of the substitution variable in the script is &1. This means "get the value from the first parameter passed into the script". If you pass in 2 parameters, the 2nd should be named &2, and so on.

I used a simple insert in the example below but it just as easily have been like your insert into table1 select * from table2 where column = '&1';

I have a dropbox folder some documents I've written covering automation designer and other things:

Toad