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

  Main Index MAIN
Search Posts SEARCH
Who's Online WHO'S
Log in LOG

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


New User

Mar 7, 2013, 11:27 AM

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

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?

FILENAME: run_list_tables.ksh

d=`date +%Y%m%d`


list_tables -login /@testdb -outputFile $output_file

mailx -s "list report : $d" test@mail < $output_file

PERL Script below

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);



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);

print OUT &putFooter($dbCounter);
print OUT " *** Report End \n";


sub putTitle {
my $title = qq{
List Tables: Yesterday

sub putHeader {
my $header = qq{

OWNER Table Created


sub putRow {

my $indx = shift;
my $ln = sprintf "%-19s %-30s %-19s",

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}");


&logMessage("rcounter records found...");






Mar 8, 2013, 1:07 PM

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