Solved

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

Posted on 2012-03-14
6
911 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

776 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