Loop over sqlplus connection identifiers from in dos shell

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

Recoursively counting files by extension in a windows shell script

It took me sort of lightyears to get this tiny piece of code running. Does not seem much logic to incorporate, uuh? Some looping and counting and printing. However, scripting this old windows shell eventually turned out a nightmare .. Ok, I learned a lot on the way to this script, about delayed expansion in nested loops (blocks) or assignments of arithmetic expressions. But the persisting lesson learned is: next time install python or something comparable first (or go and learn power shell).

Well, at the end of the day i again and again noticed how much I profit from the knowledge available on the net. So I’m going to share this script back for everyone having an interest.

Remarks:

• get me a count of all files of type (or extension) %1, recoursing any dir within the current dir
• the pushd / popd stuff is used because %%A cannot be used following “for /r” 😉
• the !CNT! is used because the changes in the inner loop are not visible to the outer one 😉 😉

echo off
setlocal enabledelayedexpansion
for /f %%A in ('dir /b/a:d') do (
  pushd %%A
  set CNT=0
  for /r %%X in (*.%1) do ( set /a CNT+=1 )
  echo %%A !CNT!
  popd
)

Enjoy!