Solved

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

Posted on 2012-03-14
6
926 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

 
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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a 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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

623 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