Suppressing Variable Substitutions and Header in Oracle SQL-Scripts

So today I had another problem: I had an sql-script which should run on a database which changes ip-address from time to time (and does not have a domain name that would be found by any DNS). So I wanted to pass teh ip-address as a parameter, as well as the username and password. Imagine the script is called mySQLScript.sql, so it would be called from the command line as follows:

sqlplus -s /nolog @mySQLScript.sql /nolog $HOST $USER $PWD

The variables $HOST, $USER and $PWD have to be initialized before.

What I want to do with the script, is to select rows from table and then write them into a csv-file. So mySQLScript will look like the following:

CONNECT &2@&1/&3;
SELECT a, b, c, d FROM table;

To write the csv-file I want to use awk like this:

sqlplus -s /nolog @mySQLScript.sql /nolog $HOST $USER $PWD  \
	| awk '{print "\"" $1 "\",\"" $2 "\"," $3 "," $4}' \
	> csv.dat

But now I have the problem, that there is a line with the table-header in it, and one with the minuses, which seperate the heading from the data. And much worse: I have these lines which tell me that I was substituting something and say something like:

old 1: $1
new 1: myUserName

That’s not what I want. So after long searches, I found, that I can simply put these two lines in my script and everything works fine:

SET HEADING OFF;
SET VERIFY OFF;

The first is for the heading (who would have thought that ), the second for the substitutions.

Be careful: You have to add these lines before the first verification comes up:

SET HEADING OFF;
SET VERIFY OFF;
CONNECT &2@&1/&3;
SELECT a, b, c, d FROM table;

Thanks to this post: http://www.mydatabasesupport.com/forums/oracle-server/367673-how-suppress-oracle-old-new-messages-sqlplus.html.

~ by runtimeexception on December 4, 2008.