a generic cmd caller script for oracle sqlplus sql script

:: File: sqlplus-runner-html

@ECHO off

:: 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 IS %0

:: ECHO MyDriveLetter is %MyDriveLetter%

:: ECHO MyPath is %MyPath%

:: ECHO MyName is %MyName%

:: ECHO MyExtension is %MyExtension%

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

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

:: 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 Pass=0024plapla

:: define the name of the service 

set TnsServiceName=ORASERVICENAME

:: define the log file 

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

:: define the error log file 

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

:: clear the result of the previous runs 

del /q %MyDir%\*.lst

del /q %MyDir%\*.log

:: Action !!!

:: call with the listener service name 

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

:: call without the listener service name 

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

:: rename the lst file to an html file 

rename *.lst *.html

:: Purpose: 

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

:: client

:: Requirements:

:: Windows XP or newer

:: sqlplus client on local windows

:: access to oracle database 

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

:: Usage: 

:: call from dos or double-click in Explorer

:: VersionHistory

:: 1.1.0 --- ysg --- Removed listener name 

:: 1.0.0 --- ysg --- Initial version