how-to run an oracle report with sqlplus and sent it via e-mail ( 1)

:: File: sqlplus-reporter v.1.2.0 docs at the end 

@ECHO off

call GetNiceTime.cmd 

:: go the run dir

cd %~dp0

:: this is the dir containing the batch file

set MyDir=%CD%

for %%A in (%0) do set MyDriveLetter=%%~dA

for %%A in (%0) do set MyPath=%%~pA

for %%A in (%0) do set MyName=%%~nA

for %%A in (%0) do set MyExtension=%%~xA

:: ECHO MyName                                             %0

:: ECHO MyDriveLetter is                                  %MyDriveLetter%

:: ECHO MyPath is                                            %MyPath%

:: ECHO MyName is                                          %MyName%

:: ECHO MyExtension is                                    %MyExtension%

:: the ora script is the same as the file name + the sql extension

set OraSqlScript=%MyDir%\%MyName%.sql

:: the perl script is the same as the file name + the pl extension

set PerlScript=%MyDir%\sendEmail.pl

:: define the log file 

set MailLogFile=%MyDriveLetter%\%MyPath%\%MyName%.log

:: define the error log file 

set MailErrorLogFile=%MyDriveLetter%\%MyPath%\%MyName%.error.log

:: define the text file holding the message 

set MessageFile=%MyDir%\%MyName%.html

:: the "from" e-mail address

set FromEmail=firstName.lastName@gmail.com

:: the "to" e-mail address 

set ToEmail=firstName.lastName@oxit.fi

:: the e-mail subject to use to the message 

set MailSubject="%MyName% --- %_NiceTime% "

:: define the text file holding the message 

set MessageFile=%MyDir%\%MyName%.html

:: the smtp server

set SmtpServerAndPort=smtp.gmail.com:587

:: define the smtp use to use for authentication against the smtp server

set SmtpUser=firstName.lastName@gmail.com

:: define the password of the smtp user against the smpt server

set SmtpUserPass=TooSecretPass

:: clear the result of the previous runs 

:: del /q %MyDir%\*.log

:: del /q %MyDir%\*.html

:: define the oracle user name to use to connect to ora

set OracleUserName=SYSMAN

:: get user input

:: SET /P Pass=[Please, provide the password for the %OracleUserName%:]

:: the pass to use to connect to the service 

SET OraPass=TooSecreteOraPass

:: define the name of the service 

set TnsServiceName=ORASERVICENAME

:: define the log file 

set OraLogFile=%MyDriveLetter%\%MyPath%\%MyName%.log

:: define the error log file 

set OraErrorLogFile=%MyDriveLetter%\%MyPath%\%MyName%.error.log

echo "%_NiceTime%" > %OraLogFile%

echo "%_NiceTime%" > %OraErrorLogFile%

echo "%_NiceTime%" > %MailLogFile%

echo "%_NiceTime%" > %MailErrorLogFile%

:: clear the result of the previous runs 

del /q %MyDir%\*.html

:: Action !!!

:: call with the listener service name 

:: sqlplus %OracleUserName%/%Pass%@%TnsServiceName @%OraSqlScript% %MyName% > %LogFile% 2>%ErrorLogFile%

:: call without the listener service name 

sqlplus %OracleUserName%/%OraPass% @%OraSqlScript% %MyName% > %OraLogFile% 2>%OraErrorLogFile%

:: DEBUG

:: PAUSE

:: Action !!!

:: perl %PerlScript% >%LogFile% 2>%ErrorLogFile%

perl %PerlScript% -v -v -l %MailLogFile% -f %FromEmail% -t %ToEmail% -u %MailSubject% -o message-file=%MessageFile% -s %SmtpServerAndPort% -o message-content-type=html -o tls=yes -o timeout=2 -xu %SmtpUser% -xp %SmtpUserPass% -a %MessageFile% %MyDir%\me.jpg

:: DEBUG

:: PAUSE

:: Purpose: 

:: create a generic wrapper for executing oracle sql commands trough the sqplplus

:: client

:: Requirements:

:: Windows XP or newer

:: GetNiceTime.cmd to generate time in YYYY.MM.DD hh:mm:ss format

:: sqlplus client on local windows

:: access to oracle database 

:: %MyName%.sql with the proper sqlplus syntax to execute the sql statememtns

:: perl sendMail.pl : http://caspian.dotconf.net/menu/Software/SendEmail/

:: access to an stmp server supporting tls 

:: Usage: 

:: call from dos or double-click in Explorer

:: VersionHistory

:: 1.2.0 --- ysg --- Added multiple attachments sending

:: 1.1.0 --- ysg --- Added GetNiceTime 

:: 1.0.0 --- ysg --- Initial version from previous tools