oracle 假脱机命令：不输出 SQL 语句到文件

Spool Command: Do not output SQL statement to file

提问by bnix

I am wanting to output a Query to a CSV file and am using the below as a small test;

我想将查询输出到 CSV 文件，并使用以下内容作为小测试；

spool c:\test.csv 
select /*csv*/ username, user_id, created from all_users;
spool off;

but the output has the actual select statment as the first line

但输出将实际的 select 语句作为第一行

> select /*csv*/ username    user_id     created from all_users
USERNAME    USER_ID CREATED
REPORT  52  11-Sep-13
WEBFOCUS    51  18-Sep-12

Is there a way to prevent this? I tried SET Heading Off thinking that might do it, but it did not change. I am using SQL Developer an running as script.

有没有办法防止这种情况？我试过 SET Heading Off 认为可能会这样做，但它没有改变。我正在使用 SQL Developer 作为脚本运行。

Thanks Bruce

谢谢布鲁斯

回答by Alex Poole

Unfortunately SQL Developer doesn't fully honour the set echo offcommand that would (appear to) solve this in SQL*Plus.

不幸的是，SQL Developer 并没有完全遵守set echo off在 SQL*Plus 中（似乎）解决这个问题的命令。

The only workaround I've found for this is to save what you're doing as a script, e.g. test.sqlwith:

我为此找到的唯一解决方法是将您正在执行的操作保存为脚本，例如test.sql：

set echo off
spool c:\test.csv 
select /*csv*/ username, user_id, created from all_users;
spool off;

And then from SQL Developer, only have a call to that script:

然后从 SQL Developer，只需调用该脚本：

@test.sql

And run that as a script (F5).

并将其作为脚本运行 (F5)。

Saving as a script file shouldn't be much of a hardship anyway for anything other than an ad hoc query; and running that with @instead of opening the script and running it directly is only a bit of a pain.

无论如何，除了临时查询之外，保存为脚本文件应该不是什么困难；运行它@而不是打开脚本并直接运行它只是有点痛苦。

---

---

A bit of searching found the same solution on the SQL Developer forum, and the development team suggest it's intentional behaviour to mimic what SQL*Plus does; you need to run a script with @there too in order to hide the query text.

一些搜索在SQL Developer 论坛上找到了相同的解决方案，开发团队建议模仿 SQL*Plus 的行为是有意的行为；您也需要在@那里运行脚本以隐藏查询文本。

回答by mdabdullah

My shell script calls the sql file and executes it. The spool output had the SQL query at the beginning followed by the query result.

我的 shell 脚本调用 sql 文件并执行它。假脱机输出的开头是 SQL 查询，然后是查询结果。

This did not resolve my problem:

这并没有解决我的问题：

set echo off

This resolved my problem:

这解决了我的问题：

set verify off

回答by Walter Mitty

set echo off
spool c:\test.csv 
select /*csv*/ username, user_id, created from all_users;
spool off;

回答by Avrajit

You can directly export the query result with export option in the result grig. This export has various options to export. I think this will work.

您可以使用结果表格中的导出选项直接导出查询结果。此导出有多种导出选项。我认为这会奏效。

回答by nikhil kodipaka

Exec the query in TOAD or SQL DEVELOPER

在 TOAD 或 SQL DEVELOPER 中执行查询

---select /*csv*/ username, user_id, created from all_users;

Save in .SQL format in "C" drive

以 .SQL 格式保存在“C”驱动器中

--- x.sql

execute command

执行命令

---- set serveroutput on
     spool y.csv
     @c:\x.sql
     spool off;