I would like to you use a "Temporary Declared Table" in an automation script. This would enable me to Query this table and send e-mails (and exported data) to various departments without having to run the Query multiple times.
This would also enable me to set Toad Variables as a result of my query, as I would be able to use a Temporary Table to save values and use Set Variable to sql result of these tables.
The problem is that the scope of a Globaly Declared Table is the session, and Toad opens a new session for each action in an Automation script.
Can this be configured differently?
we'll need to look into this. I'm opening an investigation task no. QAT-16035 for dev to take a look at.
The truth is that the multiple connection issue is wider than just the issue of Automation.
In one SQL script, Toad opens more than one connection.
This hurt me when I wanted to use a single sequece pulled from DB2 in a complicated multiple insert. We use sequences with increments of 100 that are managed by JPA (in a Websphere / RAD environment).
The way to do this is by using PREVIOUS VALUE FORsequence-name. This works only after a sequence has been pulled in the current application process. In a singe Toad SQL script this failed, as the same script sometimes used a second connection, which is considered by DB2 to be another application process.
I can send you a simple example of this, if you can't reproduce this.
P.S. I always have my Toad configured to auto-commit. I did not try the PREVIOUS VALUE FOR sequencename with the auto-commit switched to OFF.