假脱机命令:不将 SQL 语句输出到文件

2021-12-06 00:00:00 oracle oracle-sqldeveloper

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

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

spool c:	est.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 /*csv*/ username    user_id     created from all_users
USERNAME    USER_ID CREATED
REPORT  52  11-Sep-13
WEBFOCUS    51  18-Sep-12

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

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.

谢谢布鲁斯

推荐答案

不幸的是,SQL Developer 没有完全遵守 set echo off 命令,该命令可以(似乎)在 SQL*Plus 中解决这个问题.

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

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

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

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

然后在 SQL Developer 中,只需调用该脚本:

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

@test.sql

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

And run that as a script (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.

在SQL 开发者论坛和开发团队上进行了一番搜索,找到了相同的解决方案暗示模仿 SQL*Plus 的行为是有意为之;您还需要在那里运行带有 @ 的脚本以隐藏查询文本.

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.

相关文章