Automation Question: Using Variables to customize a .sql file

Toad Data Point
1

I would like to create an automation script that will send a customized spreadsheet of students assigned to a particular school, to that particular school’s assigned staff person.

I’ve used Debbie’s sample of an automation file to send a customized form letter based on values in an underlying query before and it worked well. But now I would like for each customized form letter to have an attachment that lists only the associated student.

My question is can I use the school_var I create in a set_variable activity in a SQL statement? To do so can I use the find & replace activity on a .sql file?

2

You can use your automation variable as a bind in a SQL Statement. If you are using Oracle use the bind symbol as shown below.

Select * from table
Where col = :school_var;

The automation code scans the SQL looking for bind vars and confirming that you have a automation variable of the same name. So in your case your bind var must be named school_var.

Using a bind var is the best technique, however you can use a find and replace on a text file. Try the bind first. If you have any issues email me and I will help.

Debbie