Oracle SQLPlus – Spool Query Output to File

oraclesqlplus

This is not a duplicate of "Spool query to file without output on screen"

Imagine I am spooling the below in a query:

SQL> select 'alter user '||USERNAME|| ' account lock password expire;' "Expiring Password" from dba_users where lower(username) in ('gunner','gunnersa','gunnerx3');

Expiring Password
--------------------------------------------------------------------------------
alter user GUNNER account lock password expire;

Now I have spooled off the value and saved it to a file. I will be running it as a separate file for the expiring users. When I do that, I get some basic errors about the SQL command on the dynamic query.

Is there a way that we can get only the – "alter user GUNNER account lock password expire;" from the output from SQLPlus to a file?

I use Solaris (SunOS sintscdldmu004 5.11 11.3 sun4v sparc sun4v)

Best Answer

To turn that off, use set heading off

It then should result in: 

SQL> select 'alter user '||USERNAME|| ' account lock password expire;' "Expiring Password" from dba_users where lower(username) in ('gunner','gunnersa','gunnerx3');

alter user GUNNER account lock password expire;

You probably also want to use: 

SET FEEDBACK OFF
SET TRIMSPOOL ON
SET TERM OFF
SET NEWPAGE 0

To get a "cleaner" SQL script when there are more than just three rows.

Related Question