如何在oracle sql developer中使用spool将select语句的结果正确导出到Csv文件中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15907015/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to properly Export results of select statement into Csv file using spool in oracle sql developer
提问by Hue
Code:
代码：
set heading off
set arraysize 1
set newpage 0
set pages 0
set feedback off
set echo off
set verify off
spool 'c:\farmerList.csv'
/
select FIRSTNAME','LASTNAME','TRN','CELL','PARISH
spool off
The file is being saved to the directory, however it is saving the "select FIRSTNAME','LASTNAME','TRN','CELL','PARISH" and not the results of the query in csv format. What am i doing wrong?
该文件正在保存到目录中，但是它正在保存“选择 FIRSTNAME','LASTNAME','TRN','CELL','PARISH”而不是 csv 格式的查询结果。我究竟做错了什么？
回答by Alex Poole
Your select is incomplete as you don't have a fromclause, but not sure if you've lost that in the copy-and-paste. As it is there is nothing to run, since the partial statement is never executed (no terminating ;or /on the next line). If you did have a from farmers;clause then it would show the command plus an ORA-00923 error, probably.
您的选择不完整，因为您没有from子句，但不确定您是否在复制和粘贴中丢失了它。因为它是没有什么可以运行的，因为部分语句永远不会执行（没有终止;或/在下一行）。如果您确实有一个from farmers;子句，那么它可能会显示命令以及 ORA-00923 错误。
You can't just put a quoted comma between the fields, you need to concatenate the fields with that character using the ||concatenation symbol:
您不能只在字段之间放置一个带引号的逗号，您需要使用||连接符号将字段与该字符连接起来：
spool 'c:\farmerList.csv'
select FIRSTNAME
||','|| LASTNAME
||','|| TRN
||','|| CELL
||','|| PARISH
from farmers;
gives a file containing
给出一个包含
Joe,Grundy,X,Y,Ambridge
The fields don't have to be on separate lines, I jut find that easier to read and keep track of the commas.
这些字段不必在单独的行上，我只是发现这样更易于阅读和跟踪逗号。
You don't need the /after the spool command - that will re-excute the last statement before the spool, if there is one - and you don't need the quotes around the spool file name unless it contains spaces, but they don't hurt.
您不需要/在 spool 命令之后 - 这将重新执行 , 之前的最后一条语句spool，如果有的话 - 并且除非它包含空格，否则您不需要在假脱机文件名周围加上引号，但它们不需要伤害。
There's also a set colsepcommand which you can use to make the column separator into a comma, but you have to worry about padding, so I find it easier to concatenate the columns together as you're (almost) doing.
还有一个set colsep命令可以用来将列分隔符变成逗号，但您必须担心填充，所以我发现在您（几乎）这样做时更容易将列连接在一起。
Except that's for SQL*Plus, as I didn't notice the SQL Developer reference in the title. Spool is a bit odd in Developer as it seems to trap and echo things you probably don't want, and not all of the setcommands work (which ones depends on the version).
除了 SQL*Plus 之外，因为我没有注意到标题中的 SQL Developer 参考。Spool 在 Developer 中有点奇怪，因为它似乎会捕获和回显您可能不想要的东西，而且并非所有set命令都有效（哪些取决于版本）。
The safer and preferred way, I think, is to run a normal query without concatenated commas:
我认为，更安全和首选的方法是运行没有连接逗号的普通查询：
select FIRSTNAME, LASTNAME, TRN, CELL, PARISH
from farmers;
and with 'run' rather than 'run script', so that the results appear in the grid view in the query result window. Right-click on the grid and choose 'export'. You can then save as a CSV, or even as an XLS, and can choose to not have a header row if you prefer.
并使用“运行”而不是“运行脚本”，以便结果显示在查询结果窗口的网格视图中。右键单击网格并选择“导出”。然后您可以另存为 CSV，甚至是 XLS，如果您愿意，可以选择没有标题行。
回答by peter dsouza
this is the correct solution please go through this
这是正确的解决方案，请通过这个
import java.sql.*;
import java.io.*;
import au.com.bytecode.opencsv.CSVWriter;
public class TableExport {
public static void main(String[] args) {
try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","name","password");
conn.setAutoCommit(false);
Statement statement = conn.createStatement();
ResultSet resultData = statement.executeQuery("select * from your_table");
CSVWriter writer = new CSVWriter(new FileWriter(new File("D:/Uploads/Output5.csv")), '|');
writer.writeAll(resultData, true);
writer.close();
}catch (Exception e){
System.out.println("Error" +e);
}
}
}
if anyone likes this please note you would need oracle-jdbc.jar and opencsv1.7.jar in library folder to properly execute this code.
如果有人喜欢这个，请注意你需要库文件夹中的 oracle-jdbc.jar 和 opencsv1.7.jar 才能正确执行此代码。
