oracle 在SQL Developer中使用变量从假脱机输出中删除查询

liwlm1x9 于 2022-12-11 发布在 Oracle

关注(0)|答案(3)|浏览(135)

I am trying to spool results directly to a CSV using a few substitution variables. I have this query:

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:\14-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.
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.
(SQL developer Version 4.0.0.13)
Thanks!

oracle

来源：https://stackoverflow.com/questions/24945660/remove-query-from-spool-output-in-sql-developer-with-variables

3条答案

按热度按时间

j9per5c41#

使用-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

赞(0）回复(0）举报 2022-12-11

2skhul332#

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:

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.

赞(0）回复(0）举报 2022-12-11

w8rqjzmb3#

您可以在无消息模式（sqlplus -s）下使用sqlplus。

#!/bin/bash
export ORACLE_SID=$ORACLE_SID
export ORACLE_HOME=$ORACLE_HOME
sqlplus -s / as sysdba<<EOF
set heading off
set feedback off
spool file_name
select sysdate from dual;
spool off
exit
EOF

赞(0）回复(0）举报 2022-12-11

我来回答

oracle 在SQL Developer中使用变量从假脱机输出中删除查询

3条答案

相关问题

热门标签

最新问答