We have developed an automated report with TDP that sends a commission summary out to the field. It goes out to several thousand people daily. Presently we have to use a loop of the recipients and for each recipient a subsequent query must be run to capture additional information as part of an individual select to file which is then appended with a header and footer and emailed.
The problem with this approach is that it pushes the spool capacity on the ID it's running under requiring several thousand queries to be run. What we're trying to do is consolidate their summary details into the loop, but have not confirm whether TDP can write to a file using only the output variables rather than having to run a separate select on each user.
Is it possible to write to file from within a loop without using a select statement?