khallas301
New User
Mar 7, 2013, 11:27 AM
Post #1 of 2
(102 views)
|
|
query db and email result
|
Can't Post
|
|
Friends.. this is my first post, will try to provide all details accurately and in the best format...
Final Output: Trying to query oracle db and list all tables created yesterday for particular db and email. If no
tables created 'sysdate-1' then logfile email shouldn't send.
Currently using shell script to connect db, get outputfile from perl and email to user. It runs perfectly fine but
sends email even there are no tables created.
Questions:
1. Want to only email user if any tables were created, if no table created then shell script shouldn't send any email?
2. In subroutine "putHeader" how to get db name dynamic as per login db, TEST db under subroutine is hardcoded
which is not right?
3. any easier way to include send email part within perl only so as to have only 1 file?
<code>
FILENAME: run_list_tables.ksh
#!/bin/ksh
d=`date +%Y%m%d`
log_dir=$HOME
output_file=log.list
list_tables -login /@testdb -outputFile $output_file
mailx -s "list report : $d" test@mail < $output_file
</code>
PERL Script below
<code>
FILENAME: list_tables
use strict;
use Getopt::Long;
use DBI;
use DBD::Oracle qw(:ora_types);
my $exitStatus = 0;
my %options = ()
my $oracleLogin;
my $outputFile;
my $runDate;
my $logFile;
($oracleLogin, $outputFile) = &validateCommandLine();
my $db = &attemptconnect($oracleLogin);
&reportListTables($outputFile);
$db->($exitStatus);
#---------------------------
sub reportListTables {
my $outputFile = shift;
if ( ! open (OUT,">" . $outputfile)) {
&logMessage("Error opening $outputFile");
}
print OUT &putTitle;
my $oldDB="DEFAULT";
my $dbcounter = 0;
my $i;
print OUT &putHeader();
#iterate over results
for (my $i=0; $i<=$lstSessions; $i++) {
# print result row
print OUT &putRow($i);
$dbCounter++;
}
print OUT &putFooter($dbCounter);
print OUT " *** Report End \n";
closeOUT;
}
#------------------------------
sub putTitle {
my $title = qq{
List Tables: Yesterday
--------------
};
#------------------------------
sub putHeader {
my $header = qq{
TESTDB
==============
OWNER Table Created
};
#------------------------------
sub putRow {
my $indx = shift;
my $ln = sprintf "%-19s %-30s %-19s",
$lstSessions[$indx]{owner},
$lstSessions[$indx]{object_name},
$lstSessions[$indx]{created};
return "$ln\n";
}
#------------------------------
sub getListTables {
my $runDt = shift;
my $rounter = 0;
my $SQL = qq{
selct owner, object_name, to_char(created,'MM-DD-YYYY') from dba_objects
};
my $sth = $db->prepare (SQL) or die $db->errstr;
$sth->execute() or die $db->errstr;;
while (my @row = $sth->fethcrow_array) {
$lstSessions[$rcounter] {owner} =$row[0];
$lstSessions[$rcounter] {object_name} =$row[1];
$lstSessions[$rcounter] {created} =$row[2];
&logMessage(" Owner: $lstSessions[$rcounter]{owner}");
&logMessage(" Table: $lstSessions[$rcounter]{object_name}");
&logMessage(" created: $lstSessions[$rcounter]{created}");
$rcoiunter++;
}
&logMessage("rcounter records found...");
}
</code> |
MAIN
SEARCH
WHO'S 
LOG 