Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

generate a spool without the query

Posted on 2014-02-15
2
Medium Priority
?
456 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 20

Accepted Solution

by:
flow01 earned 2000 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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

705 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