Oracle DBA by Example. Code samples, Scripts, Reference.: 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

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Sunday, November 15, 2015

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

No comments:

Post a Comment