Nearly at the same season last year, I wrote about ways and uses of piping sqlplus commands into a sqlplus session from a dos shell script (see : Piping newlined commands into sqlplus from a dos shell). The pattern worked fairly well so far such that I employed the technique more often lately to deploy code changes around database instances. However, when the piping stuff relieved me from writing that
show errors and
exit over and over again for every sql-script, I was still forced to duplicate script code over any database identifier and database session in action.
There was a point, eventually, when I felt I need to take this further, introduce a loop like in any programming language, in fact suffer this dos shell syntax quirksmode (a ss64 syntax redirection article was of great help) and just do it. Ok, it took some time to grab this
enabledelayedexpansion thing, the array syntax and so on but here you go. Regard the doubled percent signs prefixing the loop variable, the array for the
tns variable and again, the tremendous clear text password use within a script. Ahem, yes, compared to the antecessor article, you do not need the ampersand sign anymore, when the individual
echo comands occupy own distinct lines (changed it over there).
@echo off setlocal enabledelayedexpansion set USR=joe set PWD=joe set TNS=(db1 db2) chcp 1252 cd /D d:\database for %%I in %TNS% do ( (echo set timing off echo select '%USR%/%%I' as connection from dual; echo @package1.pck echo @package2.sql echo @package3.sql echo exit ) | sqlplus -s %USR%/%PWD%@%%I )
Have fun, Peter