RE: Emailing Query Results?

From: "Gogala, Mladen" <Mladen.Gogala@xxxxxxxx>
To: "'mcupp@xxxxxxxxxxxxx'" <mcupp@xxxxxxxxxxxxx>
Date: Wed, 27 Oct 2004 12:24:44 -0400
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@xxxxxxxxxx');
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@xxxxxxxxxx');

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@xxxxxxxxxxxxxxx';
    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@xxxxxxxxxxxxx] 
> 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@xxxxxxxxxxxxx
> 
> --
> //www.freelists.org/webpage/oracle-l
> 
--
//www.freelists.org/webpage/oracle-l
RE: Emailing Query Results?

Other related posts:
