RE: Emailing Query Results?

From: Stephens, Chris <ChrisStephens_at_pqa.com>
Date: Wed, 27 Oct 2004 12:36:30 -0400
Message-ID: <0C36D9C74ADA844292F3218A9C6345442B9467@exchange.pqa.local>

Mastering pl/sql has a nice emailing out of oracle implementation.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Gogala, Mladen Sent: Wednesday, October 27, 2004 12:25 PM To: 'mcupp_at_perma-fix.com'
Cc: Oracle-L_at_Freelists. Org (E-mail)
Subject: RE: Emailing Query Results?

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.=20

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

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

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

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

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

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

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

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

=20

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 ) =3D ( @_, $ENV{"TWO_TASK"} ); my $dbh =3D DBI->connect( "dbi:Oracle:$db", $username, $passwd );

    $dbh->{AutoCommit}    =3D 0;
    $dbh->{RaiseError}    =3D 1;
    $dbh->{ora_check_sql} =3D 0;
    $dbh->{RowCacheSize}  =3D 16;

return ($dbh);
}

sub send_mail {

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

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

);
$msg->attach(

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

);

$msg->send;
}

no strict;

format OUT_TOP =3D

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

format OUT=3D

@<<<<<<<<<<<<<<       @<<<<<    @<<<<<<<<<  @<<<<<<<<<
$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?
>=20
>=20
> I would like to create a query that runs nightly (maybe a procedure,=20
> kicked off from oracle jobs?) that runs a query, and if the query=20
> results in rows returned, then to have it email to a specified email=20
> account(s).  Think exception list
> - for example, select customer_no, customer_name, city, state from=20
> customers where customer_zip_code is null or
> length(customer_zip_code) not in (5,10); - This way we can tackle=20
> nightly issues that arrise before they become a large issue.  (I KNOW=20
> I KNOW - require the field - Our specific example isn't based on ZIP=20
> CODE, so I can't for other reasons)
>=20
> Has anyone done this?  Can anyone give me examples?
>=20
>=20
> Thanks,
> M
>=20
> -----
> Michael Cupp, Jr.
> Perma-Fix Industrial Services
> mcupp_at_perma-fix.com
>=20
> --
> http://www.freelists.org/webpage/oracle-l
>=20
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 27 2004 - 11:32:22 CDT