Fro Fro - 7 months ago 129
SQL Question

Remove query from spool output in SQL Developer with variables

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,
FROM table1
table1.DATE BETWEEN to_date('&&startdate') and to_date('&&enddate')
--AND table1_DATE BETWEEN '01-JAN-14' and '31-JAN-14'


...being called with this script:


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



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.