Solved

generate a spool without the query

Posted on 2014-02-15
2
446 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

685 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