We help IT Professionals succeed at work.

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

Medium Priority
963 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....
Comment
Watch Question

Sr Software Engineer
CERTIFIED EXPERT
Commented:
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
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
CERTIFIED EXPERT
Commented:
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..
David VanZandtSr Software Engineer
CERTIFIED EXPERT

Commented:
wasimibm, the author specified a comma-delimited output (CSV format), which Mike and I address.
CERTIFIED EXPERT

Commented:
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..

Author

Commented:
thank you for your help...
your suggestions help a lot..

regards,

Explore More ContentExplore courses, solutions, and other research materials related to this topic.