suppressing messages while running sql queries in a script

Question

I am trying to write a simple query-script that gets me cnt of rows in a table. However I am facing problem to suppress all sorts of oracle messages. All I am interested is the output:

Here is my script:

#!/usr/bin/ksh
sqlplus /nolog <<EOF
connect user/pswd@databse
set serveroutput on
set heading off
set feedback off
select count(*) from table;
exit;
EOF

My output looks like this:

.desktop% sh sql.ksh 
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Dec 7 12:00:42 2010
Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
SQL> Connected.
SQL> SQL> SQL> SQL> 
        70
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

All I want is the number 70 without any message so that I can write it to logs etc regularly. I know I can parse for the number but I ll have to change that every time my query or schema changes. Can't I just ask mysqlplus to suppress all those messages?

Answer 1

You have to add the uppercase S option to sqlplus.

The help message (of sqlplus that comes with Oracle 11.2.0.4.0) specifies:

-S    Sets silent mode which suppresses the display of
      the SQL*Plus banner, prompts, and echoing of
      commands.

With something like

$ sqlplus -S /nolog << EOF
connect user/pswd@databse
set serveroutput on
set heading off
set feedback off
exec package.procedure($1); -- procedure that calls DBMS_OUTPUT procedures ...
select 2 from dual;
-- ...
exit;
EOF

you only get the output from the DBMS_OUTPUT buffer and the results from select statements.