Solved

generate a spool without the query

Posted on 2014-02-15
2
436 Views
Last Modified: 2014-02-17
Hello

I want to generate a spool for only a query result.
I execute the following sql commands, but the file not contains only the result :
set heading off;
set echo off;
spool users.sql;
select username from dba_users;
spool off;

Thanks
0
Comment
Question by:bibi92
2 Comments
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 39861835
create a file containing the commands
for example qryusers.sql

then execute the command file
@qryusers.sql

you will lose the lines starting with the prompt (mostly SQL>

I have 1 empty line in front and 2 afterwards
add
set newpage none
to the command file and you loose the first empty line
I did not figure out to remove the last 2
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39864156
please don't reopen but what I think you need is:
bash# echo "select username from dba_users;" | sqlplus -S user/pass@sid >users.sql

Open in new window

or with here document and query files:
bash# sqlplus -S user/pass@sid >users.sql <<"EOF"
@qryusers.sql
EOF

Open in new window


The -S flag will beautifully trim most if not all of what you don't need.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now