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

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....
epifanio67Asked:
Who is Participating?
 
DavidSenior Oracle Database AdministratorCommented:
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
 
MikeOM_DBACommented:
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
 
Wasim Akram ShaikCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DavidSenior Oracle Database AdministratorCommented:
wasimibm, the author specified a comma-delimited output (CSV format), which Mike and I address.
0
 
Wasim Akram ShaikCommented:
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
 
epifanio67Author Commented:
thank you for your help...
your suggestions help a lot..

regards,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.