From oracle-l-bounce@freelists.org Wed Oct 27 11:20:52 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i9RGKpJ24007 for ; Wed, 27 Oct 2004 11:20:51 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i9RGKkI24001 for ; Wed, 27 Oct 2004 11:20:46 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 217B172D5AE; Wed, 27 Oct 2004 11:26:39 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 06058-28; Wed, 27 Oct 2004 11:26:38 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7825572EBAD; Wed, 27 Oct 2004 11:26:38 -0500 (EST) Message-ID: <30462D80AA52E74698512ADCC4F7EAA317C1FCA8@exchange.aetvn.com> From: "Gogala, Mladen" To: "'mcupp@perma-fix.com'" Cc: "Oracle-L@Freelists. Org (E-mail)" Subject: RE: Emailing Query Results? Date: Wed, 27 Oct 2004 12:24:44 -0400 MIME-Version: 1.0 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 11592 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Mladen.Gogala@aetn.com Precedence: normal Reply-To: Mladen.Gogala@aetn.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@oracle.com'); my ($ename,$empno,$hiredate,$sal); my $sel=< \$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@oracle.com'); END { if (defined($dbh)) { $dbh->disconnect(); } } sub usage { print "USAGE:$0 -u -p -d \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@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@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@perma-fix.com > > -- > http://www.freelists.org/webpage/oracle-l > -- http://www.freelists.org/webpage/oracle-l