Menu Content/Inhalt
Home arrow Oracle arrow tipsntricks arrow Hide SQL query in sqlplus spool file
Hide SQL query in sqlplus spool file PDF Print E-mail
Written by Martin   
Thursday, 14 January 2010
Common problem: 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 ' || username || ' 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!
Last Updated ( Friday, 14 May 2010 )
 
< Prev   Next >