Re: monthly batch reports via Oracle?

You can most very definitely build text files on the server. As an interim step I would strongly recommend using pass-through queries in Access if you aren't already.
I'd suggest writing some views to fetch and format the data, then it is fairly painless to build your text files from wherever you want.

I'm not much of a DOS expert so I can't provide the syntax for your looping. I am not sure if DOS does here documents?

Build a script with a loop.
Inside the loop you call sqlplus ( not sure what command line of sqlplus is called for Oracle 7 on NT ), direct output to a file, run the view for the loop's customer id or name.
In a previous step you could have built the list of customer ids/names, that is what you are looping over.

Mail merge software is beyond my expertise.

"zn" <zn_at_zn122.edu.invalid> wrote in message news:Xns95CAAA3DA387Eznzn122eduinvalid_at_216.196.97.131...
> We are using Oracle 7 on NT and to send montly reports to customers, we
> have a home-grown Access database that runs SQL for each customer via ODBC
> and saves the results to a text file for each customer. The report process
> takes hours as Access sends the customer's SQL query to the Oracle server,
> displays the report, saves the text file, and then moves on to the next
> customer's report. Those text files (one per customer) are then e-mailed
as
> a text attachment using a mail merge program. Is there a way to have a
> batch job run within Oracle using that same SQL string and saving the
> reports to a directory? We would like to keep the report generation on the
> server since the Access tool we are currently using ties up a workstation
> for hours and sometimes times out due to network trouble.
>
> Is it also possible to not use separate mail merge software and instead
> have Oracle e-mail the reports out?
>
> Thanks.
Received on Sun Dec 26 2004 - 18:15:04 CST