Toad Automation

Toad Data Point
1

Good Afternoon Everyone,

I am trying to create a Toad Automation Script for a process my team does. If I had a Toad Professional License, I could do it pretty easily through Cross Database SQL, but because I do not, and it doesn't appear I will any time soon, I was hoping I could do something like this:

  1. Create a Variable for Report 1 (ran from Oracle), where the analyst can enter in a month and year to run the report.
  2. Run Report 1, with the parameter defined in the variable above.
  3. Use the results from Report 1, to then connect to another Database (Teradata) to run report 2.
  4. Use the results from Report 2, to then connect back to Oracle, and run report 3.

So far, steps 1 and 2 are working. I have taken the results from Report 1, exported them to an Excel file, the connected Toad to the Excel file (as apparently, I cannot connect to local storage). From that Excel file, I wrote a simple query (something like --- Select Distinct "Unique" from Sheet1), since I need the Unique item to then run the Teradata query. I then named the variable "UNI"

I cannot figure out how to then, take those results, and bring them back into the teradata query. I have already updated the query to look for :UNI, but it won't run. I keep getting this error:
[Teradata Database] [3754] Precision error in FLOAT type constant or during implicit conversions.

I understand this error to be something with the data types? However, I have tried casting, and converting to make the data types match.

Lastly, I have taken the results from Query 1, and used them to run query 2, and that works, so I am just trying to do it through Toad Automation. Any help would be greatly appreciated.

Thanks everyone.

2

Hi Chris,
I'm sorry maybe someone else will help you with this.
From our point of view, the easiest solution is just to go and buy a pro license :wink:

3

Pro edition will enable cross connection queries. You may not like the performance of that kind of query (it depends on how big the tables are that are being connected). I generally stay away from cross connection queries as much as possible or even cross server queries. Do you have a database that you can write to in Terra data or Oracle? Put the results (preferably the smaller one) into a database table on the sever that has the larger set of data (cross database queries are fast if they are on the same server). If you don't have access to a write area talk to your IT people and get one, if they disagree bog down their server with that extremely inefficient query and make them pay for the pro license. They will change their minds. Once you have your output in a database table on the same server you can run the next query using that table. I have found that I can start a cross connection query and while it is running create the table for putting the results of the first query, run the first query, write the 2nd query, run the 2nd query and get the results while the cross connection query is still running. Good luck getting your solution automated. There are some other things you can do using a loop container from the first results but that would probably be slow unless you are using it to build dynamic sql, depends on what you are actually trying to attempt.