17

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

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 /*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.

Thanks Bruce

bnix
  • 245
  • 1
  • 3
  • 6
  • possible duplicate of [How do I spool to a CSV formatted file using SQLPLUS?](http://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus) – Nick Krasnov Sep 26 '13 at 16:48

6 Answers6

26

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

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:\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:

@test.sql

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.

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • This behaviour seems to have changed in 4.2.0 (EA2); having a separate script file may no longer be necessary. May change again before the final release, of course.... – Alex Poole Mar 02 '17 at 18:12
3

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.

This did not resolve my problem:

set echo off

This resolved my problem:

set verify off
mdabdullah
  • 596
  • 1
  • 9
  • 25
1

Just for anyone who stumbles upon this (after 7+ years) ...

This works for me ... adapt it to your way of invoking sqlplus.

sqlplus -s / as sysdba 2>&1 > /dev/null <<EOF
  whenever sqlerror exit sql.sqlcode;
  set echo off 
  set verify off
  set feedback off
  set term off
  set head off
  set pages 1000
  set lines 200

  spool /tmp/whatever.output
  
  ### Your_SQL_statement_here ###
  example: select sysdate from dual;
  example: select * from V\$LOGFILE;

  spool off
EOF

-s flag here is the key to not displaying the SQL statements when runing a script.

Amit Sanghvi
  • 21
  • 3
0
set echo off
spool c:\test.csv 
select /*csv*/ username, user_id, created from all_users;
spool off;
Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
  • 1
    `set verify off set echo off set pagesize 0 spool c:\test.csv select /*csv*/ username, user_id, created from all_users;` but it is still showing the first line with the select statement. The script output in SQL Developer shows fine? – bnix Sep 26 '13 at 16:54
0

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.

Avrajit
  • 230
  • 1
  • 2
  • 8
0

Exec the query in TOAD or SQL DEVELOPER

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

Save in .SQL format in "C" drive

--- x.sql

execute command

---- set serveroutput on
     spool y.csv
     @c:\x.sql
     spool off;
stealthyninja
  • 10,343
  • 11
  • 51
  • 59
nikhil kodipaka
  • 1