• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 907
  • Last Modified:

how to store output of an sql query to a text file in oracle 11g

how to store output of an sql query to a text file in oracle 11g
0
pegahelpdesk1
Asked:
pegahelpdesk1
  • 4
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Copy of what I posted in your other question.  You should not ask the same question in different places.
-------------------------------------------------

In sqlplus use the SPOOL command.  There are a lot of sqlplus options you can set and you can actually create very nice reports.


The basic commands go like:
spool myFile.txt
select ... from table;
spool off

If you want more detailed advice on how to use sqlplus and create a specific format, ask.
0
 
slightwv (䄆 Netminder) Commented:
The alternative is a stored procedure using UTL_FILE.

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/u_file.htm#ARPLS70896
0
 
pegahelpdesk1Author Commented:
thankyou, I'll try these two and get back
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pegahelpdesk1Author Commented:
I tried the spool option and I am getting the query also in the text file, can I create a text file where only output shows up and the query used for generating the output is not written to file?
0
 
slightwv (䄆 Netminder) Commented:
>> tried the spool option and I am getting the query also in the text file

Are you copying and pasting the code I posted directly into sqlplus?

Don't do that.

Create a script file with those commands in it.  Say, myscript.sql

then execute the script:
sqlplus user/password @myscript.sql
0
 
ajexpertCommented:
Here are the steps

1.  You should create a script and embed your query in it.  Just like attached file query.sql.
2.  Create script and embed the SET commands, make sure the paths are correct.  See attached file spool_query.txt
3.  navigate to directory where spool_query.txt is stored
4.  Connect to sql plus with user name and password
5.  execute the script with @spool_query.txt and check the spool file for query output

HTH
 spool-query.txt

 query.sql
0
 
slightwv (䄆 Netminder) Commented:
pegahelpdesk1,

Can I ask why you selected the answer you did when it basically repeated everything I had already said?

0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now