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.

c:\somewhere> (echo show errors & echo exit) | sqlplus -s usr/pwd@tns @./package.sql
Package Body wurde erstellt.
Abgelaufen: 00:00:00.39
Keine Fehler.

There are two hacks I needed to combine to get show errors and exit being piped into sqlplus as a bunch of separate lines. At first, connecting the two echo commands by “&” will actually produce the newline after show errors. However, at next, only enclosing the two echo commands into “()” will in fact pipe all of the both lines to sqlplus! That is, the second echo can and will not regard the output of the first echo in any way, it will be lost.

Jep, really like it, set timing off will drop another output line:

c:\somewhere> (echo set timing off
  echo select version from v$instance;
  echo select sysdate from dual;
  echo exit
) | sqlplus -s usr/pwd@tns

ps1. Do not forget to always set your dos shell to an appropriate encoding prior to work… win1252, for example.

c:\somewhere> chcp
Active code page: 850

c:\somewhere> chcp 1252
Active code page: 1252

ps2. Using the “pwd” part of the sqlplus connect string is at your own risk, you know, someone may spy this information from a list of running processes.


Hav fun, Peter


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s