RE: Emailing Query Results?

From: Gogala, Mladen <Mladen.Gogala_at_aetn.com>
Date: Wed, 27 Oct 2004 12:24:44 -0400
Message-ID: <30462D80AA52E74698512ADCC4F7EAA317C1FCA8@exchange.aetvn.com>

Below is a simple way of doing that. You should put in your own constants and formats, but generally speaking, it should work almost as it is.

#!/usr/bin/perl -w
use strict;
use DBI;
use MIME::Lite;
use Getopt::Long;
use FileHandle;

my ($username,$password,$dest)=("scott","tiger",'Peter.Sharman_at_oracle.com'); my ($ename,$empno,$hiredate,$sal);
my $sel=<<SQL
select ename,empno,to_char(hiredate,'MM/DD/YY'),sal from emp
SQL
;
# Parse command line options
my $stat = GetOptions(

    "u|username=s" => \$username,
    "p|password=s" => \$password,
    "r|dest=s"     => \$dest,
    "h|help|?"     => \&usage

);

if ( !defined($username) || !defined($password) || !$stat ) { usage(); }

my $dbh=db_connect($username,$password); my $output="";
open(OUT,">",\$output) || die "Cannot open output:$!\n"; OUT->format_top_name("OUT_TOP");
OUT->format_name("OUT");

my $sth=$dbh->prepare($sel);

   $sth->execute();
while (($ename,$empno,$hiredate,$sal)=$sth->fetchrow_array()) {

   write OUT;
}
close(OUT);
send_mail($output,"My database report",'Peter.Sharman_at_oracle.com');

END {
  if (defined($dbh)) { $dbh->disconnect(); } }                      

sub usage {

    print "USAGE:$0 -u <username> -p <password> -d <dest. email>\n";     print "Username and password are mandatory.\n";     exit(0);
}

sub db_connect {

    my ( $username, $passwd, $db ) = ( @_, $ENV{"TWO_TASK"} );     my $dbh = DBI->connect( "dbi:Oracle:$db", $username, $passwd );
$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;
$dbh->{ora_check_sql} = 0;
$dbh->{RowCacheSize} = 16;

    return ($dbh);
}

sub send_mail {

    my ( $data, $subject, $address ) = @_;     my $me='bestdeals_at_spread-spam.com';
    my $msg = MIME::Lite->new(

        From    => $me,
        To      => $address,
        Subject => $subject,
        Type    => "multipart/mixed",

    );
$msg->attach(

        Type        => 'TEXT',
        Data        => $data,
        Disposition => "inline"

    );

$msg->send;

}

no strict;

format OUT_TOP =

ENAME                 EMPNO     HIREDATE    SAL
------------------------------------------------------

.

format OUT=

@<<<<<<<<<<<<<<       @<<<<<    @<<<<<<<<<  @<<<<<<<<<
$ename,               $empno,   $hiredate,  $sal

.

--
Mladen Gogala
A & E TV Network
Ext. 1216


> -----Original Message-----
> From: Michael Cupp, Jr. [mailto:mcupp_at_perma-fix.com] 
> Sent: Wednesday, October 27, 2004 11:11 AM
> To: Oracle-L
> Subject: Emailing Query Results?
> 
> 
> I would like to create a query that runs nightly (maybe a 
> procedure, kicked off from oracle jobs?) that runs a query, 
> and if the query results in rows returned, then to have it 
> email to a specified email account(s).  Think exception list 
> - for example, select customer_no, customer_name, city, state 
> from customers where customer_zip_code is null or 
> length(customer_zip_code) not in (5,10); - This way we can 
> tackle nightly issues that arrise before they become a large 
> issue.  (I KNOW I KNOW - require the field - Our specific 
> example isn't based on ZIP CODE, so I can't for other reasons)
> 
> Has anyone done this?  Can anyone give me examples?
> 
> 
> Thanks,
> M
> 
> -----
> Michael Cupp, Jr.
> Perma-Fix Industrial Services
> mcupp_at_perma-fix.com
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 27 2004 - 11:20:52 CDT