Martins Blog

Trying to explain complex things in simple terms

Hide SQL query in spool file

Posted by Martin Bach on January 14, 2010

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!
About these ads

5 Responses to “Hide SQL query in spool file”

  1. [...] 18-How to hide sql query from spool file ? Martin Bach-hide SQL query in spool file [...]

  2. Madhu said

    Thanks Martin , i was scratching my head to achieve this , I too tried with trimspool on.But it didn’t work.Thanks once again

  3. Madhu said

    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

Leave a Reply

Fill in your details below or click an icon to log in:

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

 
Follow

Get every new post delivered to your Inbox.

Join 2,273 other followers

%d bloggers like this: