Fr3dy,
Thanks once more for your suggested solution.
For the sake of completeness, below you’ll find the version I have crafted.
Kind regards,
Abe Kornelis.
@ECHO OFF
rem Script Launcher by Fr3dY v1.5
rem ##############################
rem Version History:
rem 1.5c (Abe Kornelis) determine path for PLINK only if .sh script requested
rem 1.5b (Abe Kornelis) added support for optional parms to indicate the script and db/server filenames
rem 1.5a (Abe Kornelis) adapted to Kramp environment, added showlog routine
rem 1.5 - Error messages are saved in the log file (instead stdout)
rem 1.4 - Misc. fixes
rem 1.3 - Merged with ‘server launcher’, now accepts both SQL and SHELL SCRIPTS
rem 1.2 - Interactive prompt to show the file on screen
rem 1.1 - No need to add ‘quit;’ or ‘exit;’ in the .sql file anymore
rem Fixed sqlplus waiting for username/password if the first attempt was unsuccessful
rem Log file is generated automatically, including date and time in name
rem Misc. fixes
rem 1.0 - Initial Version
:SETUP
set listfile=
set scriptfile=
if %1. EQU . goto MAIN
if exist %1.txt set listfile=%1
if exist %1.sql set scriptfile=%1
if exist %1.sh set scriptfile=%1
exho l=%listfile%. s=%scriptfile%.
if %2. EQU . goto MAIN
if exist %2.txt set listfile=%2
if exist %2.sql set scriptfile=%2
if exist %2.sh set scriptfile=%2
:MAIN
if %1. EQU . echo Optionally, you can enter as parameters the name of a script file and/or a targets file (both without extension)
::List with TNS NAMES
if defined listfile (if exist %listfile%.txt (set dbservers=%listfile%.txt) else (set dbservers=launcher-databases.txt)) else (set dbservers=launcher-databases.txt)
::List with LINUX SERVERS
if defined listfile (if exist %listfile%.txt (set linuxservers=%listfile%.txt) else (set linuxservers=launcher-databases.txt)) else (set linuxservers=launcher-databases.txt)
set dt=%DATE:~9,4%%DATE:~6,2%%DATE:~3,2%__%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
set dt=%dt: =0%
if defined scriptfile (
if exist %scriptfile%.sql (
set servers=%dbservers%
set extension=sql
set mode=DB
) else (
set servers=%linuxservers%
set extension=sh
set mode=OS
)
goto CHECKSERVERS
)
echo Choose launcher mode:
echo 1) Database scripts (.sql files)
echo 2) Shell scripts (.sh files)
set /p launchermode="Enter desired mode: "
echo.
if %launchermode%==1 (
set extension=sql
set servers=%dbservers%
set mode=DB
) else (
if %launchermode%==2 (
set extension=sh
set servers=%linuxservers%
set mode=OS
) else (echo “Incorrect value, exiting…” & goto :END)
)
:CHECKSERVERS
if exist %servers% (
goto :LISTFILES
) else echo FILE %servers% NOT FOUND, ABORTING & goto :END
:LISTFILES
if defined scriptfile set file=%scriptfile%& goto confirmexec
echo Listing *.%extension% files…
echo.
dir /on /b *.%extension%
echo.
set /p file=Name of the file to be launched (without extension)?
if exist %file%.%extension% (
goto :CONFIRMSHOW
) else echo FILE %file%.%extension% NOT FOUND, ABORTING & goto :END
:CONFIRMSHOW
echo.
set /p confirm=Show the script %file%.%extension% on screen now? (Y/N)
if /I %confirm%==y (
goto :SHOWSCRIPT
) else goto :CONFIRMEXEC
echo.
:SHOWSCRIPT
echo.
echo Content of %file%.%extension%
echo ######################
type %file%.%extension%
echo.
echo ######################
echo.
:CONFIRMEXEC
set /p confirm=Are you sure you want to execute %file%.%extension% against each %mode% in %servers%? (Y/N)
if /I %confirm%==y (
set /p user=%mode% username?
goto :HInput
) else echo ABORTED & goto :END
echo.
Goto :END
:HInput
::Hidden.cmd
::Tom Lavedas, 02/05/2013, 02/20/2013
::Carlos, 02/22/2013
::https://groups.google.com/forum/#!topic/alt.msdos.batch.nt/f7mb_f99lYI
::Version 3.0
SetLocal DisableDelayedExpansion
echo.
Echo Enter password:
Set “Line=”
Rem Save 0x08 character in BS variable
For /F %%# In (
‘“Prompt;$H&For %%# in (1) Do Rem”’
) Do Set “BS=%%#”
:HILoop
Set “Key=”
For /F “delims=” %%# In (
‘Xcopy /L /W “%~f0” “%~f0” 2^>Nul’
) Do If Not Defined Key Set “Key=%%#”
Set “Key=%Key:~-1%”
SetLocal EnableDelayedExpansion
If Not Defined Key echo. & Goto :HIEnd
If %BS%==^%Key% (Set /P “=%BS% %BS%” <Nul
Set “Key=”
If Defined Line Set “Line=!Line:~0,-1!”
) Else Set /P “=*” <Nul
If Not Defined Line (EndLocal &Set “Line=%Key%”
) Else For /F delims^=^ eol^= %%# In (
“!Line!”) Do EndLocal &Set “Line=%%#%Key%”
Goto :HILoop
:HIEnd
if %mode%==DB (
goto :EXECDB
) else goto :EXECLINUX
:EXECDB
FOR /f %%A IN (%servers%) DO CALL ECHO DATABASE: %%A & ECHO DATABASE: %%A >> $logs%file%%dt%.log & sqlplus -S -L %user%/!Line!@%%A < %file%.%extension% >> $logs%file%%dt%.log
goto :SHOWLOG
:EXECLINUX
::Path of PLINK (default)
set PLINK=“C:\Program Files (x86)\PuTTY\plink.exe”
:CHECKPLINK
if exist “%PLINK%” goto EXECLINUX2
set /p “PLINK=%PLINK% not found. Enter path to plink.exe "
if “%PLINK%”. EQU . echo ABORTED & goto END
:: if path only was entered, add plink.exe
if exist “%PLINK%”\plink.exe set PLINK=”%PLINK%"\plink.exe
goto CHECKPLINK
:EXECLINUX2
FOR /f %%A IN (%servers%) DO CALL ECHO SERVER: %%A & ECHO SERVER: %%A >> $logs%file%%dt%.log & echo y | %PLINK% %user%@%%A -pw !Line! “exit” & %PLINK% %user%@%%A -pw !Line! -batch -m %file%.%extension% >> $logs%file%%dt%.log 2>>&1 & echo. >> $logs%file%_%dt%.log
goto :SHOWLOG
:SHOWLOG
echo.
if exist $logs%file%%dt%.log (echo Output saved to $logs%file%%dt%.log) else (echo No log file created)
set /p confirm=Show log file? (Y/N)
if /I %confirm% NEQ y goto END
echo.
echo Content of $logs%file%%dt%.log
echo ######################
type $logs%file%%dt%.log
echo.
echo ######################
echo.
:END
