Solved

Oracle: sqlplus: how to send query results to .csv file locally?

Posted on 2012-03-14
6
906 Views
Last Modified: 2012-03-14
Hello Experts,

is there a way to run a query in sqlplus and send the results to a local file?
any format would do....

thx....
0
Comment
Question by:epifanio67
6 Comments
 
LVL 23

Accepted Solution

by:
David earned 167 total points
ID: 37721041
A number of approaches, and of course, sources.  Here's a useful one if you're going to need to reuse the script:  http://www.oracle-base.com/dba/Script.php?category=miscellaneous&file=csv.sql
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 167 total points
ID: 37721663
Whatever script/procedure you choose out there, it may need some "customization" as follows:

1) Change the field delimiter to something like pipe "|" (or similar) to avoid column values that may have embedded comma(s) or the delimiter.

2) Quote all string type (varchar2/char) values to prevent #1.

3) Use TO_CHAR() to set a standard date/time format and also enclose it in quotes.

Good luck!
:p
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 166 total points
ID: 37721949
just login to sqlplus and do something like this

SQL> spool "C:\test.txt"
SQL> select sysdate from dual;

SYSDATE
---------
15-MAR-12
SQL> spool off;
SQL>

This will write the query and results to the file test.txt in your C:\ directory..
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:David
ID: 37721963
wasimibm, the author specified a comma-delimited output (CSV format), which Mike and I address.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37721974
sorry its typo, just forgot to replace txt with csv, this thing too would suffice the requirement of the author..!!

author, replace .txt with .csv in my comments..
0
 

Author Closing Comment

by:epifanio67
ID: 37722323
thank you for your help...
your suggestions help a lot..

regards,
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

911 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

22 Experts available now in Live!

Get 1:1 Help Now