Re: Mailing DB Reports

Niall Litchfield wrote:

> Is it possible to email a ref cursor using utl_smtp (or indeed javamail
> etc)? I have a need to mail the output from a number of similar select
> statements to an applications administrator on a scheduled basis. I'd
> therefore quite like to use dbms_job to automate this procedure. At the
> moment I achieve this by using OS scheduling and a shell script to mail the
> spooled output of a sqlplus session from my workstation. This fails dismally
> when I go on leave and turn the workstation off. It also isn't exactly the
> best documented procedure in the world!
>
> Alternatively how do others do this sort of thing? utl_file? OEM Reports?
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************

Yes but not the way you are thinking. In a loop you would need to concatenate it all together into one or more strings.

But I'm not sure what you would do when you hit the 32K limit of a VARCHAR2. I've never tried UTL_SMTP with a CLOB. But I can think of a possible work-around using the old DBMS_SQL and its ability to load data into a PL/SQL table. Just haven't tried it.

Daniel Morgan Received on Wed Aug 28 2002 - 11:30:01 CDT