Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2012-03-14
6
Medium Priority
?
940 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 668 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 668 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 664 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

569 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