Trying to dynamicly build Oracle scripts, that will be run by someone else, later. The query that builds the input variable works and returns 125 rows, of latest input. But, while looping, only the very first variable is used, over and over for 125 times. For the output, only the very first variable is installed in the output. What am I doing wrong?
TDA newbie
All_VC_ViewsTEST_20111201a.log (129 KB)
VC_2R2011-12-01 14-43-55.txt (67 Bytes)
VC_2R2011-12-01 16-47-38.txt (67 Bytes)
VC_SQL.sql (105 Bytes)
VC_TemplateR.txt (72 Bytes)
VC_TemplateTEST.txp (1.25 KB)
VC_TemplateTESTb.txp (1.35 KB)
Honestly, I’m terrible with looping constructs. It looks as though your Loop_Row1 process is resetting the variable View_Name, via the Set_Variable_3 activity, to the results of a query. I think it should be referencing the View_Name variable as its value is the result of the query. You set it as such in the Loop_Data_1 activity. So, rather than referencing the query result, it looks as though you’re simply re-executing the query.
This blog on ToadWorld may provide a better example of how this can be done; in particular, steps 3, 4, and 5 seem to parallel what you’re trying to accomplish: http://www.toadworld.com/Blogs/tabid/67/EntryId/498/Automation-Variables-Automation-variables-using-datasets.aspx
Hope this makes sense.
Great, the looping works after starting from scratch and following each step! The output only shows the very last Loop output. What would be the recommended way of outputting all of this, to like a text file?
I’m afraid I’m of little help here. My guess would be using the log comment within the loop_row construct. Others might have better suggestions.
Sorry I have not answered this post. I have been on vacation. I agree with everything that Ben has posted.
Make those changes and then if still doesn’t work post the latest tas file and I will have a look.
Debbie
Ben’s solution solved the first part of my problem. Now it it looping properly. The second part is that the looping is overwriting the output.
VC_3c.tas (9.33 KB)
Or another way to look at this, is instead of email in the above link, I am trying to save all in a text file, without it overwriting (append). Thanks
Attached is a sample script of what you want to accomplish. Unzip the files into your C:\temp directory. Then create an Excel connection to Views.xlsx before running the automation script. This script also connects and uses the Toad SampleDatabase which you should already have a connection to.
The basic correction is to add an additional variable. You need to have two variables. One is the dataaset, which you have (view_name). But for each row of view_name you need a variable to hold the name of the view. So you have to set a new variable to the row value of view_name. This is the variable you use for the find and replace. In my example I have also used this variable for the name of the exported file.
Debbie
GetViews.zip (7.7 KB)