0

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)

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.