CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
query db and email result

 



khallas301
New User

Mar 7, 2013, 11:27 AM

Post #1 of 2 (317 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>

Code



      
    


wickedxter
User

Mar 8, 2013, 1:07 PM

Post #2 of 2 (297 views)
Re: [khallas301] query db and email result [In reply to] Can't Post

my guess is that your bash script needs to check the results coming from the perl script either for greater then 1 or check if the returning results.

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives