What is the correct format to pass a variable from a query iterator to a child script execution in the automation designer? The following scenario doesn't appear to work:
Parent:
select col from table /* where col returns an int stored as varchar */
Child:
select col from table where col = "%Parent.col%"
- ORA-00904: "%PARENT.CHILD%": invalid identifier
nor
select col from table where col = '%Parent.col%'
- ORA-01722: invalid number
nor
select col from table where col = %Parent.col%
- ORA-00911: invalid character
Toad Data Point binds with a colon or hash, which do not work. I was expecting some continuity with respect to syntax, but that doesn't appear to be the case.
The following post includes reference to using a file and parameter, but that doesn't appear to work either.
If I use '&1' it returns ORA-01722: invalid number, where the value returned is 5340615288.
If I use &1 or "&1" it returns ORA-00904: "PARENT"."CHILD": invalid identifier.
The problem I have run into with Toad products and bind variables is with data type conversion. I have experienced this with Toad Data Point, but eventually found I needed to cast a date, for example, to char from a parent query to avoid conversion issues with bind variables. In this case I don't see how an integer value would encounter this problem.
I have a dropbox folder here with a lot of documents in it. One of them describes how to pass variables into scripts in automation designer.
Thanks, I found a reference to using the query iterator in the "run a script in a list of schemas" file, which worked. The spool reference would be a useful add to any documentation.
Is there a way to run it in silent mode so it will suppress the window on each execution?
Also how do you kill it during execution?
If you just right-click to run an action, there is no way to cancel.
If you double-click an action to open its properties window, then run it from there, some types of actions can be cancelled and some cannot. (for example, Compare Schemas can be cancelled. The iterators, as far as I know, cannot. That would be a good enhancement)
There is no way to suppress the script execution dialog from Toad that I'm aware of, but if you were to run the action from command line, then it would not show.
There is another potential issue. If I set the parent query to filter to a specific record and then run the iterator the child script will insert a record without fail. As soon as a I remove the filter on the parent to allow it to iterate through all records it appears to process very quickly, but no inserts are taking place. Any idea why it would be any different?
I even added an IN to the parent to see if it would process a small list of values and no records were inserted.
I assume by "filter" you mean "where clause", not filtering the grid by clicking a column header. That info isn't recorded in the action.
hard to say. You could turn on spool sql to see what is happening.
Main menu -> database -> spool sql -> spool to screen.
The log is useful, but it's showing me it's executing, but not completing. Does each record get committed or do we have to specify that in the script?
I ran what was in the log and it returned a record so it's not really clear how the insert wouldn't complete, having already tested it successfully with a lone record.
That was it. Oracle hadn't received a commit so once I added it the records appeared. I guess that makes sense, but didn't realize it had to be added explicitly.
I don't know why you'd see the record for the single exec but not multi, but (ok I see your reply)....yeah, I was about to say, Toad doesn't commit for you. You'd have to put that in yourself.
Depending on options, it could be that where you were looking for the records was in a different session than the action's script.
That's fine. I appreciate the assist. Seems to work, but a kill button would be nice. Fortunately task manager works and it doesn't kill the script runner, which I guess is a separate thread.
I'll look at adding the ability to cancel. This is kind of an involved change. We'll add this for the version after 16.2. We're a little to close to 16.2's release date to add big changes.
I've read over the command line notes in the help file, but it sounds like tode.exe doesn't have a silent mode like data point does. Is that the case? If I can reduce the overhead by not opening the IDE it might perform better. I kicked off a process and came back to find it had hung and erred out.
You can run apps or actions silently from command line.
The syntax is: "full path to Toad.exe" -a "App/Action Name".
Look in main menu -> help -> contents.
Go to the "Index" tab and search for "Command line".
There are plenty of examples.
You don't have to specify "silent" when running from command line. It's just automatically silent.
