Perl, Batch, sqlplus code example. Generate extract from several DB.

====================
General
====================
Connect to DB listed in ini file.
And append output to one HTML type output.

====================
Files List
====================
refresh_report_main.bat
refresh_report_main.pl
main_from_perl.bat
run_sql.bat
refresh_log_sql.sql

refresh_report.ini
db_list.ini
last_db_report.txt
gates_refresh_log_stats.txt

====================
Files
====================
refresh_report_main.bat
perl refresh_report_main.pl

refresh_report_main.pl
#! /usr/bin/perl
#Daily process for reading from refresh_log table on gates

use DateTime;

use strict;
use warnings; 

#===================
#Global Parameters
#===================
my $INI_FILE='refresh_report.ini'; 
my $LOG_FILE_BASE_NAME='Gates_Refresh_Log';
my $display_delimiter="========================================\n";
my %hashTable;

my $MAIN_HOME_WIN='';
my $BAT_CMD = '';
my $MAIL_CMD= '';
my $MAIL_HEADER='';
my $MAIL_FOOTER='';
my $MAIL_PATH='';

my $INI_FILE_FOR_DB;
my $DB_LOG='';
my $LOG_FILE_PATH='';
my $LOG_FILE_NAME='';
my $REPORT_FILE_PATH='';
my $REPORT_FILE='';
my $LAST_DB_REPORT_FILE='';

my $timestamp='';
my $logFile='';
my $dbLogFile='';
my $reportFile='';
my $mailCmd='';
my $mailHeaderFile='';
my $mailFooterFile='';
my $lastReportFile;
my $run_date='';
my $result='';
#==================================
# subs
#==================================

sub setRundate{
  my $dateTime=DateTime->now;
  my $month=$dateTime->month;
  my $year=$dateTime->year;
  
  if ($month < 10){ $month="0".$month ; }
  my $day=$dateTime->day;
  if ($day < 10){ $day="0".$day ; }
  my $hh=$dateTime->hour;
  if ($hh < 10){ $hh="0".$hh ; }
  my $mm=$dateTime->minute;
  if ($mm < 10){ $mm="0".$mm ; }
  my $ss=$dateTime->second;
  if ($ss < 10){ $ss="0".$ss ; }

  $run_date=$dateTime->year.$month.$day;
  
  $timestamp=$run_date."_".$hh.$mm.$ss;
  print "Running time: ".$timestamp."\n";
}

sub fileFullPathNames{
$reportFile=$REPORT_FILE_PATH."\\".$REPORT_FILE."_".$timestamp.".rep";
$dbLogFile=$LOG_FILE_PATH."\\".$DB_LOG;
$mailCmd=$MAIL_PATH."\\".$MAIL_CMD;
$mailHeaderFile=$MAIL_PATH."\\".$MAIL_HEADER;
$mailFooterFile=$MAIL_PATH."\\".$MAIL_FOOTER;
}


sub setLogFileName{
  $logFile=$LOG_FILE_PATH."\\".$LOG_FILE_NAME."_".$timestamp.".log";
  my $status=0;
  #print "Will try to create now new folder: ".$LOG_FILE_PATH;
  if (!-d "$LOG_FILE_PATH" ){
my $cmd = "mkdir "."\"".$LOG_FILE_PATH."\"";
$status=system($cmd);
  }
  if ($status == 0 ){  
    print "\n";
    print $display_delimiter;
    print "\n";
    print "Log File: ".$logFile."\n";
    print "\n";
    print $display_delimiter;
    print "\n";
  }
  $status;
}

sub readIniFile{
  my $i_ini_file=shift;
  
  my $line='';
  my $key='';
  my $value='';    
  my $match_str = "CUSTOMER_[0-9][0-9]"; 
  
  #read from ini file
  open(FILE_HANDLER, "$i_ini_file") or die ("Could not open ini file: "."$i_ini_file"."\n");
  my $count=0;
  foreach $line (<FILE_HANDLER>){
    chomp($line);
    if ($line =~ /^$/) { 
#nothing. Ignore empty lines
    }elsif($line =~ /^#/) { 
#nothing. Ignore # comment lines lines
}else{
 ($key, $value)=split(/=/,$line);
  $hashTable{$key} = $value;   
   }
 }
 close (FILE_HANDLER);
}

sub sendMail{
  my @mailArg = "";
  my $sendMailInd=0;
  print "Running command: ".$mailCmd." to send contents of file: ".$reportFile;
  @mailArg=($mailCmd,$reportFile);
  system(@mailArg);
}

sub printMultipath{
  my $str = shift;
  my $log_handler = shift;
    print $log_handler ($str);
print ($str);  
}

sub setGeneralParams{
   #from ini file
   $MAIN_HOME_WIN = $hashTable{"MAIN_HOME_WIN"}; 
   $INI_FILE_FOR_DB=$hashTable{"INI_FILE_FOR_DB"};
   $BAT_CMD = $hashTable{"BAT_CMD"}; 
   $DB_LOG=$hashTable{"DB_LOG"};
   $REPORT_FILE=$hashTable{"REPORT_FILE"};
   $MAIL_CMD = $hashTable{"MAIL_CMD"};
   $MAIL_HEADER = $hashTable{"MAIL_HEADER"};
   $MAIL_FOOTER = $hashTable{"MAIL_FOOTER"};
   $LAST_DB_REPORT_FILE= $hashTable{"LAST_DB_REPORT_FILE"};
   
   #concatinations
   $LOG_FILE_PATH=$MAIN_HOME_WIN."\\"."Logs";
   $REPORT_FILE_PATH=$MAIN_HOME_WIN."\\"."Reports";
   $LOG_FILE_NAME=$LOG_FILE_BASE_NAME."_".$timestamp.".log";
   $MAIL_PATH=$MAIN_HOME_WIN."\\"."Mail";
   $lastReportFile=$MAIN_HOME_WIN."\\".$LAST_DB_REPORT_FILE;
   #report
   print $display_delimiter;
   print "Running With General Parameters:"."\n";
   print "MAIN_HOME_WIN: ".$MAIN_HOME_WIN."\n";
   print "BAT_CMD: ".$BAT_CMD."\n";
   print "INI_FILE_FOR_DB: ".$INI_FILE_FOR_DB."\n";
   #print "MAIL_CMD: ".$MAIL_CMD."\n";
   print "DB_LOG: ".$DB_LOG."\n";
   print "Report: ".$REPORT_FILE."\n";
   print $display_delimiter;
}


sub runOsCommand{

my $log_handler = shift;
my $cmd = shift;
my $ini_file = shift;
print "Running Command: ".$cmd."\n";
#$result=system($^X,$cmd);
    my @SendArg=($cmd,$ini_file, $dbLogFile);

$result=system(@SendArg);

#print "Exit point after call to ".$cmd." Exist status is: ".$result."\n";
$result;
}
sub updateLastDbReport{
  my $lastReportFile=shift;
  my $reportFile=shift;  
  
  open OUT_FILE,">",$lastReportFile or die $!;
  print OUT_FILE $reportFile."\n";
  close OUT_FILE or die $!;
}
sub formatReportFile{
    my $log_handler = shift;
my $sourceFile = shift;
my $targetFile = shift;
my $header=shift;
my $footer=shift;
    $result=system($^X,"format_db_report.pl",$sourceFile,$targetFile, $header,$footer);
$result;
}

sub reportGeneralParameters{
  my $log_handler = shift;
  
  printMultipath ("\n",$log_handler);
  printMultipath ($display_delimiter,$log_handler);
  printMultipath ("Input Parameters: \n",$log_handler);
  printMultipath ("Run Date:".$run_date."\n",$log_handler);
  printMultipath ("Log File: ".$logFile."\n",$log_handler);
  printMultipath ("MAIN_HOME_WIN: ".$MAIN_HOME_WIN."\n",$log_handler);    
  printMultipath ("BAT_CMD: ".$BAT_CMD."\n",$log_handler);    
  printMultipath ("MAIL_CMD: ".$MAIL_CMD."\n",$log_handler);    
  printMultipath ($display_delimiter,$log_handler);
  printMultipath ("\n",$log_handler);
}

sub reportStartOfProcess{
    my $log_handler = shift;

printMultipath ("\n",$log_handler);
    printMultipath ($display_delimiter,$log_handler);
printMultipath ("Refresh Log On Gates Checkup Process Started at: ".$timestamp."\n",$log_handler);
printMultipath ($display_delimiter,$log_handler);
printMultipath ("\n",$log_handler);
}
sub reportEndOfProcess{
    my $log_handler = shift;
    my $status = shift;

printMultipath ("\n",$log_handler);
    printMultipath ($display_delimiter,$log_handler);
if ($status == 0){
printMultipath ("Process has Finished Successfuly"."\n",$log_handler);

}else{
printMultipath ("Error!!!! Process has Terminated with Error."."\n",$log_handler);
}
printMultipath ($display_delimiter,$log_handler);
printMultipath ("\n",$log_handler);

$status;
}

sub handleStatus{
  my $status = shift;
  my $method = shift;
  my $log_handler = shift;
  
  if ($status != 0){
    if (defined $log_handler){
printMultipath ("Error in Routine: ".$method." Status: ".$status."\n",$log_handler);
}else{
print ("Error in Routine: ".$method." Status: ".$status."\n");
}
return $status;
  }
  $status;
}


#==================================
# The code starts here
#==================================

   $result=main();
   exit $result; 
   
#==================================
# sub main
#==================================
sub main{   
   no warnings 'uninitialized'; 

   setRundate();    
   readIniFile("$INI_FILE"); 
   setGeneralParams();
   $result=setLogFileName();      
   handleStatus($result, "setLogFileName",undef); 
   open my $LOG_HANDLER, '>', $logFile or die ("Could not open log file: ".$logFile); 
   fileFullPathNames();
   reportStartOfProcess($LOG_HANDLER);  
   reportGeneralParameters($LOG_HANDLER);  
   my $cmd='"'.$MAIN_HOME_WIN."\\".$BAT_CMD.'"';
   $result=runOsCommand($LOG_HANDLER,$cmd,$INI_FILE_FOR_DB);
   handleStatus($result, $cmd,undef); 
   #$result=formatReportFile($LOG_HANDLER,$dbLogFile,$reportFile,$mailHeaderFile,$mailFooterFile);
   $result=formatReportFile($LOG_HANDLER,$dbLogFile,$reportFile,"NULL","NULL");
   handleStatus($result, "formatReportFile",undef); 
   updateLastDbReport($lastReportFile,$reportFile);
   reportEndOfProcess($LOG_HANDLER, $result); 
   close $LOG_HANDLER; 
   #sendMail(); - mail is send from a server with access to mail server
}

main_from_perl.bat
ECHO OFF

SET inifile=%1%
SET REFRESH_LOG=%2%
SET TEMP_REFRESH_LOG=temp_gates_refresh_log_stats.txt

ECHO.
ECHO =============================================
ECHO main is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%REFRESH_LOG%

FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO handling %%i
ECHO. 2>%TEMP_REFRESH_LOG%
    call run_sql.bat %TEMP_REFRESH_LOG% %%i
    TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================


run_sql.bat
SET spool_to_file=%1
SET db_name=%2
REM ECHO got DB name: %db_name%
REM ECHO refresh_log_sql %db_name%
sqlplus -l -s %db_name% @refresh_log_sql %spool_to_file% %db_name%

refresh_log_sql.sql
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD ON
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON

SPOOL &&1
SELECT '&&2' AS GATE_NAME, COUNT(*) AS REFRESH_LOG_ENTRIES FROM REFRESH_LOG;
SPOOL OFF
EXIT;
/

refresh_report.ini

MAIN_HOME_WIN=L:\refresh_report

INI_FILE_FOR_DB=db_list.ini

BAT_CMD=main_from_perl.bat

DB_LOG=gates_refresh.log

REPORT_FILE=refresh_log_report

MAIL_CMD=sendMail.bat

MAIL_HEADER=mail_header.txt

MAIL_FOOTER=mail_footer.txt

LAST_DB_REPORT_FILE=last_db_report.txt

db_list.ini

vip700/vip700@AGO-MOVIC-01_VNODE

vip700/vip700@ALB-VODAF-01_VNODE

vip700/vip700@ARG-PERSO-01_VNODE

vip700/vip700@AUS-VODAF-01_VNODE

vip700/vip700@AUT-CONNE-01_VNODE

vip700/vip700@AZE-AZERF-01-VNODE

vip700/vip700@BGR-MOBIL-01_VNODE

vip700/vip700@BOL-TELEC-01_VNODE

vip700/vip700@BRA-TIMRN-01_VNODE

vip700/vip700@CAN-ROGWI-01_DNODE

====================
Output Files
====================

last_db_report.txt

L:\refresh_report\Reports\refresh_log_report_20151108_140031.rep

gates_refresh_log_stats.txt

GATE_NAME                        REFRESH_LOG_ENTRIES
-------------------------------- -------------------
vip700/vip700@PRT-TELEC-01_VNODE                  17

GATE_NAME               REFRESH_LOG_ENTRIES
----------------------- -------------------
vip700/vip700@BGR-MOBIL               89539