This is a common problem I have: I need to generate a SQL script to be called from another SQL script in SQLplus. Best example: export user names, using this command for example:
select 'alter user ' || username || ' identified by values ''' || password || ''';' from dba_users;
To avoid the usually useful output in this case I used to set a few options, such as in:
set heading off pages 0 trimspool on lines 120 feedback off echo off termout off
Inside a shell script this could look as follows:
$ORACLE_HOME/bin/sqlplus / as sysdba <<EOF set heading off pages 0 trimspool on lines 120 feedback off echo off termout off spool /tmp/users.sql select 'alter user ' || name || ' identified by values ''' || password || ''';' from dba_users; exit EOF
However, the resulting file users.sql contains the SQL command! I didn’t find a “set” option in SQLPlus to get rid of it so I ended up “grep”ping for “^alter” to only have the SQL commands. Then I found out that I could as well use sqlplus -S (capital S) to achieve the same. Very handy!
Martins Blog 
Responses
[…] 18-How to hide sql query from spool file ? Martin Bach-hide SQL query in spool file […]
Thanks Martin , i was scratching my head to achieve this , I too tried with trimspool on.But it didn’t work.Thanks once again
Better try to create a sql file with all your commands. Eg. cmd.sql and then try running it along with your login. I do see some strange thing with a .sh file.
sqlplus ‘/as sysdba’ @cmd.sql
Not sure if better, but certainly another approach, thanks for sharing.
Yes it is, because it will remove the SQL command. Interactive commands are always echoed to screen AND spool file.
“sqlplus -s” works… s means silent.
Nice karthi… it’s worked
Thanks Martin
Saved a lot of time.