dos shell

Piping newlined commands into sqlplus from a dos shell

sqlplus indeed does not offer a wealth of options on the command line. When it comes to running sqlplus in a dos shell the prospects even go worse for executing some batch logic outside the sql script itself (I still did not manage to find me some time to learn more abount power shell, maybe things work out smarter there). Say, for example, you want to deploy a package body to a number of instances but have no option to inject the sqlplus commands show errors and exit into the package body file. All you can do that far is setting up a batch script, probably unsing the “-s” and “@” options of sqlplus, containing a three-liner for each and every instance like so:

sqlplus -s usr/pwd@tns @./package.sql
show errors
Package Body wurde erstellt.
Abgelaufen: 00:00:00.42
show errors
Keine Fehler.

For a large number of instances, the actually (my point) superfluous two lines of show errors and exit may lead to tedious batch scripts containing almost redundant commands. Knowing how to pipe commands into scripts from un*x, I investigated options to behave comparably on windows and dos shell. Not some 15 minutes later, see the refs below, i came up with the following approach that makes the three-liner a one-liner (like that), takes no change against the package body file whatsoever and even reduces the output by another two lines.


Redirecting dbv output to a file

this is a short note for everyone, who is still wondering why a simple shell construct like a redirect operator to a file won’t work for dbv (dbverify).

imagine a situation where you’re about to dbv-scan a good couple of datafiles and want to concatenate the output into a summary file. one approach is, probably, to just redirect/concatenate the output of dbv using >, create or cleanup the file, and >>, append the file, like so (please do not kick me for the weird “>” formatting below, the view plain link will show it correctly):

dbv file=c:\data01.dbf > dbv.log
dbv file=c:\data02.dbf >> dbv.log

however, the resulting file will be empty. the reason is, that for whatever sake, dbv does not write write to channel stdout, as one might expect, but to channel stderr (???). a simple trick to circumvent this issue is to overlay stdout and stderr by writing:

dbv file=c:\data01.dbf > dbv.log 2>&1
dbv file=c:\data02.dbf >> dbv.log 2>&1

here, the channel stdout is denoted by 1 and stderr by 2, respectively. to have it complete, channel stdin is usually identified by 0, if anyone may need to know it.

the same is, by the way, true for loadjava.