如何在 Oracle SQL Developer 中使用 spool 命令将查询结果输出到 CSV 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26431218/
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 output query results to CSV file using spool command in Oracle SQL Developer
提问by b00kgrrl
I am trying to output the results of a SQL query to a CSV file using the SPOOL command in Oracle SQL Developer.
我正在尝试使用 Oracle SQL Developer 中的 SPOOL 命令将 SQL 查询的结果输出到 CSV 文件。
I am able to output the results of a trivial query by calling it as a script. Here is the query that worked:
我可以通过将其作为脚本调用来输出简单查询的结果。这是有效的查询：
spool trivial_output.csv
select /*csv*/ * from trivial_table;
spool off;
And this is how I successfully called it (F5):
这就是我成功调用它的方式（F5）：
@'C:\Spool to CSV\trivial_query.sql'
However, when I attempt the exact same thing with a slightly more complex query, I get the error message: "SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - SQL command not properly ended"
但是，当我使用稍微复杂的查询尝试完全相同的操作时，我收到错误消息：“SQL 错误：ORA-00933：SQL 命令未正确结束 00933。00000 - SQL 命令未正确结束”
spool total_records.csv
select /*csv*/ enrol.year, enrol.college, count(*) as "Total Records"
from enrolment enrol
inner join regis_status_type regstatus
on enrol.regis_status_type_id = regstatus.regis_status_type_id
where enrol.year in ('201213', '201314')
and regstatus.regis_status_type_code in ('10','41')
group by enrol.year, enrol.college
order by enrol.year, enrol.college
spool off;
回答by b00kgrrl
I just needed to add a semi-colon to separate the SQL*Plus command from the SQL statement. Thanks to Justin Cave
我只需要添加一个分号来将 SQL*Plus 命令与 SQL 语句分开。感谢贾斯汀·凯夫
spool total_records.csv
select /*csv*/ enrol.year, enrol.college, count(*) as "Total Records"
from enrolment enrol
inner join regis_status_type regstatus
on enrol.regis_status_type_id = regstatus.regis_status_type_id
where enrol.year in ('201213', '201314')
and regstatus.regis_status_type_code in ('10','41')
group by enrol.year, enrol.college
order by enrol.year, enrol.college;
spool off;
回答by Shine
spool "D:\test\test.txt"
select
a.ename
from
employee a
inner join department b
on
(
a.dept_id = b.dept_id
)
;
spool off
This query will spool the sql result here D:\test\test.txt
此查询将在 D:\test\test.txt 中后台处理 sql 结果
