oracle 使用变量从 SQL Developer 的假脱机输出中删除查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24945660/
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
Remove query from spool output in SQL Developer with variables
提问by Fro
I am trying to spool results directly to a CSV using a few substitution variables. I have this query:
我正在尝试使用一些替换变量将结果直接假脱机到 CSV。我有这个查询：
SET echo off
set feedback off
set pagesize 0
set termout off
define startdate='12-JAN-14'
define enddate='01-JUN-14'
define vpath=C:-0724_Spool testing\'
define dt='60_testupdate_subvar'
define ext='.csv'
SPOOL &&vpath&&dt&&ext
SELECT /*csv*/ table1.SOURCE_DMS_ID,
COUNT(table1. AMOUNT)
FROM table1
WHERE
table1.DATE BETWEEN to_date('&&startdate') and to_date('&&enddate')
--AND table1_DATE BETWEEN '01-JAN-14' and '31-JAN-14'
GROUP BY table1_DIM.SOURCE_DMS_ID;
...being called with this script:
...被这个脚本调用：
@"\Results_Feed_Spooled.sql"
I specifically call it with a script (and SET various other items) so that the resulting CSV file will NOT have the query in the file. However, the select query DOES appear in the CSV file. Interestingly, when I switch the where clause to be an actual date (see the commented section of the query) instead of a reference to the substitution variable, the query does NOT appear in the resulting CSV.
我专门用脚本（和 SET 各种其他项目）调用它，以便生成的 CSV 文件不会在文件中包含查询。但是，选择查询确实出现在 CSV 文件中。有趣的是，当我将 where 子句切换为实际日期（请参阅查询的注释部分）而不是对替换变量的引用时，查询不会出现在生成的 CSV 中。
Why does using the variable in the WHERE clause instead of actual values cause the query to be included in the result file? How can I correct that? It is important to the usefulness of the script that I am able to use the variables.
为什么在 WHERE 子句中使用变量而不是实际值会导致查询包含在结果文件中？我该如何纠正？我能够使用变量对脚本的有用性很重要。
(SQL developer Version 4.0.0.13)
（SQL 开发人员版本 4.0.0.13）
Thanks!
谢谢！
回答by justMe
Use -s. The -s flag means silent, a complete bash script will look like something as follow:
使用-s. -s 标志表示静默，一个完整的 bash 脚本如下所示：
#!/bin/bash
sqlplus -s /nolog << EOF
CONNECT USER/PASSWORD
set echo off
set verify off
set colsep ","
set linesize 9999
set trimout on
set trimspool on
set heading on
set pagesize 0 embedded on
set wrap off
set feedback off
set newpage 0
set arraysize 5000
set serveroutput off
spool "/tmp/exported_date/table.csv"
SELECT * FROM TABLE;
spool off;
EXIT;
EOF
回答by TenG
Assuming SQL_Developer is sqlplus compliant, first, try adding "SET VERIFY OFF" . If that doesn't work you can also try putting the spool after the query as in the following example:
假设 SQL_Developer 与 sqlplus 兼容，首先，尝试添加 "SET VERIFY OFF" 。如果这不起作用，您还可以尝试将 spool 放在查询之后，如下例所示：
set verify off
define x = 'X'
select * from dual where '&x' = 'X'
spool x.out
/
spool off
Note the blank line after the SELECT, and the absence of the semi-colon after the SELECT statement.
请注意 SELECT 之后的空行，以及 SELECT 语句之后没有分号。
